Recent comments

None


İçerik Ara











Yasal Uyarı
Bu sitede sunulan tüm bilgi ve dökümanlar Turgay Sahtiyan tarafından yazılmaktadır. Yazıların kaynak göstermek şartıyla kullanılması serbesttir.

© Copyright 2009-2013
Takvim
<<  Ağustos 2017  >>
PaSaÇaPeCuCuPa
31123456
78910111213
14151617181920
21222324252627
28293031123
45678910
Keywords

Merhaba arkadaşlar

Bugün sizin ile 2 analist raporu paylaşmak istiyorum.

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bu sıralar suspect duruma düşmüş DB’lerin nasıl kurtarılabileceği üzerine oldukça fazla soru görmekteyim. Bu konu üzerine Paul Randal’ın yazdığı aşağıdaki yazıyı paylaşmak istiyorum.

Not olarak şunu düşmek istiyorum ki, eğer bir database suspect durumdaysa sakın detach etmeyin, yoksa bir daha attach etme şansınız olmayabilir.

http://sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bugün size SQLServerCentral.com da rastladığım 2 SP den bahsetmek istiyorum.

1. script ile DB de bulunan table lar için create scriptleri ve constraint create scriptleri generate edebilirsiniz. Bunları drop ettikten sonra bu script i kullanarak tekrar create edebilirsiniz.

2.script ile ise gene table ve bu table lara ait constraint leri drop etme scriptlerini generate edebilirsiniz.

Ben bu 2 SP yi bin sonraki yazım olan Change DB Collaction (Database Collaction ını Değiştirme) makalemde kullanacağım.

Table ve Constraint leri Create etmek için DDL Script Generate Etme


SP nin create script i aşağıdaki gibidir. Bu script i AdventureWorks DB sinde create edelim

CREATE PROCEDURE [dbo].[usp_Create_Table_DDLs]
    (
        @sTable_Name        SYSNAME,
        @Create_Table_Ind    BIT = 1,
        @PK_Ind                BIT = 1,
        @FK_Ind                BIT = 1,
        @Check_Ind            BIT = 1,
        @Default_Ind        BIT = 1
    )
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @Schema_Name        SYSNAME,
            @UniqueConstraints    BIT = 1,
            @sStr                VARCHAR(MAX)

    SELECT    @Schema_Name = SCHEMA_NAME(schema_id)
    FROM    sys.objects
    WHERE    name = @sTable_Name

    IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE ID = OBJECT_ID('tempdb..#PKObjectID'))    DROP TABLE #PKObjectID
    IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE ID = OBJECT_ID('tempdb..#Uniques'))        DROP TABLE #Uniques
    IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE ID = OBJECT_ID('tempdb..#Constraints'))    DROP TABLE #Constraints
    IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE ID = OBJECT_ID('tempdb..#ShowFields'))    DROP TABLE #ShowFields

    CREATE TABLE #Constraints (ID INT IDENTITY, Constraint_Type VARCHAR(100), SQL VARCHAR(8000), Column_Name SYSNAME DEFAULT '')

    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------

    -- Create table
    IF @Create_Table_Ind = 1
    BEGIN
        SELECT    FieldID                    = IDENTITY(INT,1,1),
                DatabaseName            = DB_NAME(),
                TableOwner                = TABLE_SCHEMA,
                TableName                = TABLE_NAME,
                FieldName                = COLUMN_NAME,
                ColumnPosition            = CAST(ORDINAL_POSITION AS INT),
                ColumnDefaultValue        = COLUMN_DEFAULT,
                ColumnDefaultName        = dobj.name,
                IsNullable                = CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,
                DataType                = DATA_TYPE,
                MaxLength                = CAST(CHARACTER_MAXIMUM_LENGTH AS INT),
                NumericPrecision        = CAST(NUMERIC_PRECISION AS INT),
                NumericScale            = CAST(NUMERIC_SCALE AS INT),
                DomainName                = DOMAIN_NAME,
                FieldListingName        = COLUMN_NAME + ',',
                FieldDefinition            = '',
                IdentityColumn            = CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END,
                IdentitySeed            = CAST(ISNULL(ic.seed_value,0) AS INT),
                IdentityIncrement        = CAST(ISNULL(ic.increment_value,0) AS INT),
                IsCharColumn            = CASE WHEN DATA_TYPE NOT IN ('TEXT') AND st.collation_name IS NOT NULL THEN 1 ELSE 0 END
        INTO    #ShowFields
        FROM    INFORMATION_SCHEMA.COLUMNS            c
                JOIN sys.columns                    sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
                LEFT JOIN sys.identity_columns        ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
                JOIN sys.types                        st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
                LEFT OUTER JOIN sys.objects            dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'
        WHERE    c.TABLE_NAME = @sTable_Name
        ORDER    BY c.TABLE_NAME, c.ORDINAL_POSITION

        
        SELECT    @sStr = 'CREATE TABLE ' + QUOTENAME(@Schema_Name) + '.' + QUOTENAME(@sTable_Name) + '('

        SELECT    @sStr = @sStr + 
                CHAR(10) + CHAR(9) + QUOTENAME(FieldName) + ' ' +
                    CASE
                        WHEN DomainName IS NOT NULL THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
                        ELSE UPPER(DataType) 
                                + CASE WHEN IsCharColumn = 1 OR DataType IN ('Varbinary') THEN '(' + CASE WHEN MaxLength = -1 THEN 'MAX' ELSE CAST(MaxLength AS VARCHAR(10)) END + ')' ELSE '' END 
                                + CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END 
                                + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END 
                                --+ CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END
                    END + 
                    CASE WHEN FieldID = (SELECT MAX(FieldID) FROM #ShowFields) THEN '' ELSE ',' END
        FROM #ShowFields
        
        SELECT    @sStr = @sStr + ')'
                
        INSERT    INTO #Constraints (Constraint_Type, SQL)
        VALUES    ('CREATE_TABLE', @sStr)            
    END
    
    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------
    
    IF @PK_Ind = 1
    BEGIN
        PRINT 'Creating SQL for PK and Unique Constraints ...'

        -- Get Object ID of the PK
        SELECT    DISTINCT ObjectID = cco.object_id
        INTO    #PKObjectID
        FROM    sys.key_constraints            cco
                JOIN sys.index_columns        cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
                JOIN sys.indexes            i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
        WHERE    OBJECT_NAME(parent_object_id) = @sTable_Name 
        AND        i.type = 1 
        AND        is_primary_key = 1

        ---------------------------------------------------------------------------

        -- Get Object ID of the Uniques
        SELECT    DISTINCT ObjectID = cco.object_id
        INTO    #Uniques
        FROM    sys.key_constraints            cco
                JOIN sys.index_columns        cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
                JOIN sys.indexes            i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
        WHERE    OBJECT_NAME(parent_object_id) = @sTable_Name 
        AND        i.type = 2 
        AND        is_primary_key = 0 
        AND        is_unique_constraint = 1 

        ---------------------------------------------------------------------------

        INSERT    INTO #Constraints (Constraint_Type, SQL)
        SELECT    'PK_UNIQUE_CONSTRAINT',
                [PK_UNIQUE_CONSTRAINTS] = ISNULL('ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(@sTable_Name) + ' ADD CONSTRAINT ' 
                    + QUOTENAME(cco.name )
                    + CASE    type WHEN 'PK' THEN ' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN 'NONCLUSTERED ' ELSE 'CLUSTERED ' END
                                 WHEN 'UQ' THEN ' UNIQUE ' 
                     END 
                    + CASE    WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END 
                    + '(' + REVERSE(SUBSTRING(REVERSE((
                                                        SELECT    c.name + + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
                                                        FROM    sys.key_constraints            ccok
                                                                LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
                                                                LEFT JOIN sys.columns        c ON cc.object_id = c.object_id AND cc.column_id = c.column_id
                                                                LEFT JOIN sys.indexes        i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
                                                        WHERE    i.object_id = ccok.parent_object_id 
                                                        AND        ccok.object_id = cco.object_id
                                                        FOR        XML PATH('')
                                                     )
                                                     ), 2, 8000)) + ')', '')
        FROM    sys.key_constraints            cco
                INNER JOIN sys.schemas        s ON cco.schema_id = s.schema_id
                LEFT JOIN #PKObjectID        pk ON cco.object_id = pk.ObjectID
                LEFT JOIN #Uniques            u ON cco.object_id = u.objectID
        WHERE    OBJECT_NAME(cco.parent_object_id) = @sTable_Name
        AND        (type = 'PK'
        OR         type = CASE WHEN @UniqueConstraints = 1 THEN 'UQ' ELSE 'PK' END
                )
        --AND        s.name = @TableSchema
    END

    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------

    IF @FK_Ind = 1
    BEGIN
        PRINT 'Creating SQL for FK Constraints ...'

        -- Create all FKs for a table
        INSERT    INTO #Constraints (Constraint_Type, SQL)
        SELECT    'FK_CONSTRAINT',
                [FK_CONSTRAINTS] = 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' WITH CHECK ADD CONSTRAINT ' + QUOTENAME(a.name) + ' FOREIGN KEY (' + a.ParentColumns + ') REFERENCES ' + QUOTENAME(a.ReferencedSchema) +'.' + QUOTENAME(a.ReferencedObject) + '(' + a.ReferencedColumns + ')'
        FROM
            (
                SELECT    fk.OBJECT_ID as object_id,
                        ReferencedSchema    = SCHEMA_NAME(o.Schema_ID),
                        ReferencedObject    = OBJECT_NAME(fk.referenced_object_id), 
                        ParentObject        = OBJECT_NAME(fk.parent_object_id),
                        Name                = fk.name,
                        ParentColumns        = REVERSE(SUBSTRING(REVERSE((
                                                    SELECT    cp.name + ','
                                                    FROM    sys.foreign_key_columns fkc
                                                            JOIN sys.columns        cp    ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
                                                    WHERE    fkc.constraint_object_id = fk.object_id
                                                    FOR        XML PATH('')
                                                 )
                                                 ), 2, 8000)),
                        ReferencedColumns    = REVERSE(SUBSTRING(REVERSE((
                                                    SELECT    cr.name + ','
                                                    FROM    sys.foreign_key_columns fkc
                                                            JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
                                                    WHERE    fkc.constraint_object_id = fk.object_id
                                                    FOR        XML PATH('')
                                                 )
                                                 ), 2, 8000)) 
                FROM    sys.foreign_keys fk
                        INNER JOIN sys.objects o ON fk.referenced_object_id = o.object_id
            ) a
            INNER JOIN sys.objects    co ON a.object_id = co.object_id            
            INNER JOIN sys.objects    o ON co.parent_object_id = o.object_id
            INNER JOIN sys.schemas    s ON o.schema_id = s.schema_id
        WHERE a.ParentObject = @sTable_Name
        ORDER BY a.name
    END

    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------

    IF @Check_Ind = 1
    BEGIN
        PRINT 'Creating SQL for Check Constraints ...'

        -- Create check constraints for all the columns of a table
        INSERT    INTO #Constraints (Constraint_Type, SQL)
        SELECT    'CHECK_CONSTRAINT',
                [CHECK_CONSTRAINTS] = 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' WITH CHECK ADD CHECK ' + cc.definition + ';'
        FROM    sys.check_constraints cc
                INNER JOIN sys.objects co ON cc.object_id = co.object_id
                INNER JOIN sys.objects o ON co.parent_object_id = o.object_id
                INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE    OBJECT_NAME(cc.parent_object_id) = @sTable_Name
        ORDER    BY o.name
    END

    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------

    IF @Default_Ind = 1
    BEGIN
        PRINT 'Creating SQL for Default Constraints ...'
            
        -- Create defaults for all the columns of a table
        INSERT    INTO #Constraints (Constraint_Type, Column_Name, SQL)
        SELECT    'DEFAULT_CONSTRAINT',
                Column_Name = c.name,
                [DEFAULT_CONSTRAINTS] = 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' ADD DEFAULT ' + sc.text + ' FOR ' + c.name
        FROM    syscomments    sc
                INNER JOIN syscolumns c ON sc.id = c.cdefault
                INNER JOIN sys.objects o ON c.id = o.object_id
                INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE    sc.id    IN    (
                                SELECT    cdefault 
                                FROM    syscolumns
                                WHERE    id = OBJECT_ID(@Schema_Name + '.' + @sTable_Name) 
                                AND        cdefault > 0
                            )
        ORDER    BY c.name
    END

    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------
    

    SELECT    * 
    FROM    #Constraints
    WHERE    (1 = 1
    OR         Constraint_Type = CASE WHEN @Default_Ind = 1    THEN 'DEFAULT_CONSTRAINT'        ELSE '' END
    OR         Constraint_Type = CASE WHEN @Check_Ind = 1        THEN 'CHECK_CONSTRAINT'            ELSE '' END
    OR         Constraint_Type = CASE WHEN @PK_Ind = 1        THEN 'PK_UNIQUE_CONSTRAINT'        ELSE '' END
    OR         Constraint_Type = CASE WHEN @FK_Ind = 1        THEN 'FK_CONSTRAINT'            ELSE '' END
            )
    ORDER    BY ID

    ---------------------------------------------------------------------------

    PRINT 'Complete.'
END
-- EXEC usp_Create_Table_DDLs @sTable_Name = 'MY_TABLE', @Create_Table_Ind = 1, @PK_Ind = 1, @FK_Ind = 1, @Check_Ind = 1, @Default_Ind = 1

GO

 

Kullanımına bakacak olursak;

Örneğin AdventureWorks DB sinde bulunan Sales.SalesOrderDetail tablosu için create script generate etmek istersek aşağıdaki kodu kullanabiliriz.

EXEC usp_Create_Table_DDLs 
	@sTable_Name = 'SalesOrderDetail'
	, @Create_Table_Ind = 1
	, @PK_Ind = 1
	, @FK_Ind = 1
	, @Check_Ind = 1
	, @Default_Ind = 1

 

Parametrelerde ki 1 value ları scriptlerin generate edileceğini gösteriyor. 0 verirseniz o parametre ile alakalı scriptler generate edilmeyecektir.


Table ve Constraint leri Drop etmek için DDL Script Generate Etme


SP nin create script i aşağıdaki gibidir. Bu script i AdventureWorks DB sinde create edelim.

CREATE PROCEDURE [dbo].[usp_Drop_Table_Constraints]
    (
        @sConstraint_Type    VARCHAR(50),        -- PK, FK, CK, DF
        @sDB_Name            SYSNAME,
        @sTable_Name        SYSNAME,
        @sColumn_Name        SYSNAME = NULL
    )
AS
BEGIN
    SET NOCOUNT ON
    
    DECLARE    @sSQL            VARCHAR(8000),
            @sStr            VARCHAR(1000)
    
    CREATE TABLE #Temp (
        Type                VARCHAR(50),
        DBName                SYSNAME,
        Schema_Name            SYSNAME,
        Table_Name            SYSNAME,
        Column_Name            SYSNAME DEFAULT '',
        Constraint_Name        SYSNAME,
        DROP_SQL            VARCHAR(8000)
    )

    -------------------------------------------------------------
    
    IF @sConstraint_Type = 'PK'
    BEGIN
        -- Drop PK
        SET @sSQL = '    
                        SELECT    DISTINCT Type        = ''PK'',
                                DBName                = ''' + @sDB_Name + ''',
                                Schema_Name            = s.name,
                                Table_Name            = p.name,
                                PK_Constraint_Name    = o.name,
                                DROP_SQL            = ''ALTER TABLE ' + @sDB_Name + '.'' + s.Name + ''.'' + c.Table_Name + '' DROP CONSTRAINT '' + c.CONSTRAINT_NAME
                        FROM    ' + @sDB_Name + '.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
                                INNER JOIN ' + @sDB_Name + '.sys.objects o ON c.CONSTRAINT_NAME = o.name
                                INNER JOIN ' + @sDB_Name + '.sys.objects p on o.parent_object_id = p.object_id
                                INNER JOIN ' + @sDB_Name + '.sys.schemas s ON p.schema_id = s.schema_id
                        WHERE    c.TABLE_NAME = ''' + @sTable_Name + '''
                        AND        o.type = ''PK''
                    '
                    
        INSERT INTO #Temp (Type, DBName, Schema_Name, Table_Name, Constraint_Name, DROP_SQL)
        EXEC (@sSQL)
    END

    -------------------------------------------------------------

    ELSE IF @sConstraint_Type = 'FK'
    BEGIN
        -- Drop FK
        SET @sSQL = '
                        SELECT    DISTINCT Type        = ''FK'',
                                DBName                = ''' + @sDB_Name + ''',
                                Schema_Name            = s.name,
                                Table_Name            = p.name,
                                FK_Constraint_Name    = o.name,
                                DROP_SQL            = ''ALTER TABLE ' + @sDB_Name + '.'' + s.Name + ''.'' + c.Table_Name + '' DROP CONSTRAINT '' + c.CONSTRAINT_NAME
                        FROM    ' + @sDB_Name + '.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
                                INNER JOIN ' + @sDB_Name + '.sys.objects o ON c.CONSTRAINT_NAME = o.name
                                INNER JOIN ' + @sDB_Name + '.sys.objects p on o.parent_object_id = p.object_id
                                INNER JOIN ' + @sDB_Name + '.sys.schemas s ON p.schema_id = s.schema_id
                        WHERE    c.TABLE_NAME = ''' + @sTable_Name + '''
                        AND        o.type = ''F''
                    '

        INSERT INTO #Temp (Type, DBName, Schema_Name, Table_Name, Constraint_Name, DROP_SQL)
        EXEC (@sSQL)
    END

    -------------------------------------------------------------
    
    ELSE IF @sConstraint_Type = 'CK'
    BEGIN
        -- Drop Check Constraint
        SET @sSQL = '    
                        SELECT    DISTINCT Type        = ''CK'',
                                DBName                = ''' + @sDB_Name + ''',
                                Schema_Name            = s.name,
                                Table_Name            = p.name,
                                Column_Name            = c.column_name,
                                CK_Constraint_Name    = o.name,
                                DROP_SQL            = ''ALTER TABLE ' + @sDB_Name + '.'' + s.Name + ''.'' + c.Table_Name + '' DROP CONSTRAINT '' + c.CONSTRAINT_NAME
                        FROM    ' + @sDB_Name + '.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
                                INNER JOIN ' + @sDB_Name + '.sys.objects o ON c.CONSTRAINT_NAME = o.name
                                INNER JOIN ' + @sDB_Name + '.sys.objects p on o.parent_object_id = p.object_id
                                INNER JOIN ' + @sDB_Name + '.sys.schemas s ON p.schema_id = s.schema_id
                        WHERE    c.TABLE_NAME = ''' + @sTable_Name + '''
                        AND        o.type = ''C''
                    '
        INSERT INTO #Temp (Type, DBName, Schema_Name, Table_Name, Column_Name, Constraint_Name, DROP_SQL)
        EXEC (@sSQL)
    END

    -------------------------------------------------------------

    ELSE IF @sConstraint_Type = 'DF'
    BEGIN
        -- Drop Default Constraint
        SET @sSQL = '
                        SELECT    DISTINCT Type        = ''DF'',
                                DBName                = ''' + @sDB_Name + ''',
                                Schema_Name            = s.name,
                                Table_Name            = p.name,
                                Column_Name            = c.name,
                                DF_Constraint_Name    = o.name,
                                DROP_SQL            = ''ALTER TABLE ' + @sDB_Name + '..'' + p.name + '' DROP CONSTRAINT '' + o.name 
                        FROM    ' + @sDB_Name + '.sys.columns c 
                                INNER JOIN ' + @sDB_Name + '.sys.objects o on c.default_object_id = o.object_id
                                INNER JOIN ' + @sDB_Name + '.sys.objects p on o.parent_object_id = p.object_id
                                INNER JOIN ' + @sDB_Name + '.sys.schemas s ON p.schema_id = s.schema_id
                        WHERE    o.type = ''D'' 
                        AND        c.object_id = object_id(''' + @sDB_Name + '..' + @sTable_Name + ''')
                    '
        INSERT INTO #Temp (Type, DBName, Schema_Name, Table_Name, Column_Name, Constraint_Name, DROP_SQL)
        EXEC (@sSQL)
    END

    -------------------------------------------------------------
    
    SELECT    *
    FROM    #Temp

    -------------------------------------------------------------
    
END

/*

EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'PK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'    -- Drop PK
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'FK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'    -- Drop FKs
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'CK', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'    -- Drop Check Constraints
EXEC usp_Drop_Table_Constraints @sConstraint_Type = 'DF', @sDB_Name = 'MY_DATABASE', @sTable_Name = 'MY_TABLE'    -- Drop Default Constraints

*/

GO

 

Kullanımına bakacak olursak;

Örneğin AdventureWorks DB sinde bulunan Sales.SalesOrderDetail tablosundaki Primary Key constraint ler için drop script generate etmek istersek aşağıdaki kodu kullanabiliriz.

EXEC usp_Drop_Table_Constraints 
	@sConstraint_Type = 'PK'
	, @sDB_Name = 'AdventureWorks'
	, @sTable_Name = 'SalesOrderDetail'

 

Gene aynı şekilde Foreign Key(FK),Check(CK) ve Default(DF) Constraint leri drop etmek için aşağıdaki scriptleri kullanabilirsiniz.

-- Drop FKs
EXEC usp_Drop_Table_Constraints 
	@sConstraint_Type = 'FK'
	, @sDB_Name = 'AdventureWorks'
	, @sTable_Name = 'SalesOrderDetail'
-- Drop Check Constraints
EXEC usp_Drop_Table_Constraints 
	@sConstraint_Type = 'CK'
	, @sDB_Name = 'AdventureWorks'
	, @sTable_Name = 'SalesOrderDetail'
-- Drop Default Constraints
EXEC usp_Drop_Table_Constraints 
	@sConstraint_Type = 'DF'
	, @sDB_Name = 'AdventureWorks'
	, @sTable_Name = 'SalesOrderDetail'

 

Daha önce bahsettiğim gibi bu 2 SP yi Change DB Collaction (Database Collaction ını Değiştirme) makalemde kullanacağım. O makalemde görüşmek üzere.

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Geçenlerde SQL Server 2008 i repair etme ihtiyacı duydum. Gerekli dvd yi dvd rom a koyduktan sonra repair işlemine başladım. Lakin bazı hatalar verdikten sonra repair işlemi sonlandı.

Bununla alakalı araştırmalar yaparken aşağıdaki KB article ı buldum.

Görünen o ki, dvd den repair işlemi yapılınca bazı hatalar almak mümkün. MS e tekrar selam ediyoruz. :)

Bu problemin çözümü için KB article a buradan ulaşabilirsiniz.

Kolay gelsin

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Paul S. Randal (SQLskills.com) ‘ın yazdığı BLOB dataların SQL Server üzerinde FILESTREAM olarak işlenmesi üzerine çok güzel bir inceleme.

Okumanızı tavsiye ederim.

http://msdn.microsoft.com/en-us/library/cc949109.aspx

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


turgay , 1. Temmuz 2009, 10:02

Merhaba arkadaşlar,

Uzun süredir SQL Server 2008 kurulumu ile ilgili adım adım bir döküman hazırlamayı düşünüyordum.

Geçenlerde web de gezerken aşağıdaki dökümanı buldum. Böyle güzel hazırlanmış bir yazı varken ekstra zaman harcayıp yazmaya gerek görmedim:)

Buyrun afiyetle yiyiniz:)

Sql 2008 Kurulumu - http://www.cozumpark.com/blogs/sql/archive/2008/11/09/sql-server-2008-kurulum.aspx

Kolay gelsin

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan