SQL Server’da Tablo Key Alanlarının Scriptini Almak

Herkese merhaba,

Bu yazıda SQL Server’da tablo key alanlarının scriptinin nasıl alınacağından bahsedeceğim.

Aslına bakarsanız yapacağımız işlem bir tablonun key alanlarının yani Primary Key, Foreign Key ve Index Key alanalarının scriptinin oluşturulması işlemi.

Her ne kadar aşırı derecede kullanmasam da SQL Server’da bazı durumlarda sadece key alanların scriptini alma gereği duyabiliriz. Tablo bazında yapacağımız bu işlem bizi kod karmaşasından kurtarır diye de düşünüyorum. Ayrıca tablo bazlı key alanlarının yedekleme işlemi gibi bir işlem de yapılacaksa işe yarayabilir.

İlk olarak aşağıdaki prosedürü ilgili veritabanınızda oluşturmalısınız. Ben örnek olması açısından Adventureworks veritabanı üzerinde aşağıdaki prosedürü oluşturdum.

CREATE PROC [dbo].[TabloKeylerininScriptiniAl] @table_name sysname
AS
BEGIN

    SET NOCOUNT ON;


    DECLARE @crlf CHAR(2);

    SET @crlf = CHAR(13) + CHAR(10);

    DECLARE @version CHAR(4);

    SET @version = SUBSTRING(@@VERSION, LEN('Microsoft SQL Server') + 2, 4);

    DECLARE @object_id INT;

    SET @object_id = OBJECT_ID(@table_name);

    DECLARE @sql NVARCHAR(MAX);



    /*    IF @version NOT IN ( '2005', '2008' )
    BEGIN

        RAISERROR('This script only supports SQL Server 2005 and 2008', 16, 1);

        RETURN;

    END;*/



    SET @sql
        = N'' + N'SELECT ' + N'CASE ' + N'WHEN 1 IN (i.is_primary_key, i.is_unique_constraint) THEN '
          + N'''ALTER TABLE '' + ' + N'QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''.'' + '
          + N'QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + ' + N'''ADD '' + ' + N'CASE k.is_system_named '
          + N'WHEN 0 THEN ''CONSTRAINT '' + QUOTENAME(k.name) + @crlf ' + N'ELSE '''' ' + N'END + ' + N'CASE k.type '
          + N'WHEN ''UQ'' THEN ''UNIQUE'' ' + N'ELSE ''PRIMARY KEY'' ' + N'END + '' '' + ' + N'i.type_desc  + @crlf + '
          + N'kc.key_columns + @crlf ' + N'ELSE ' + N'''CREATE UNIQUE '' + i.type_desc + '' INDEX '' + '
          + N'QUOTENAME(i.name) + @crlf + ' + N'''ON '' + ' + N'QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''.'' + '
          + N'QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + ' + N'kc.key_columns + @crlf + ' + N'COALESCE ' + N'( '
          + N'''INCLUDE '' + @crlf + ' + N'''( '' + @crlf + ' + N'STUFF ' + N'( ' + N'( ' + N'SELECT ' + N'( '
          + N'SELECT ' + N''','' + @crlf + '' '' + QUOTENAME(c.name) AS [text()] ' + N'FROM sys.index_columns AS ic '
          + N'JOIN sys.columns AS c ON ' + N'c.object_id = ic.object_id ' + N'AND c.column_id = ic.column_id '
          + N'WHERE ' + N'ic.object_id = i.object_id ' + N'AND ic.index_id = i.index_id '
          + N'AND ic.is_included_column = 1 ' + N'ORDER BY ' + N'ic.key_ordinal ' + N'FOR XML PATH(''''), TYPE '
          + N').value(''.'', ''VARCHAR(MAX)'') ' + N'), ' + N'1, ' + N'3, ' + N''''' ' + N') + @crlf + '
          + N''')'' + @crlf, ' + N''''' ' + N') ' + N'END + ' + N'''WITH '' + @crlf + ' + N'''('' + @crlf + '
          + N''' PAD_INDEX = '' + ' + N'CASE CONVERT(VARCHAR, i.is_padded) ' + N'WHEN 1 THEN ''ON'' '
          + N'ELSE ''OFF'' ' + N'END + '','' + @crlf + ' + N'CASE i.fill_factor ' + N'WHEN 0 THEN '''' ' + N'ELSE '
          + N''' FILLFACTOR = '' + ' + N'CONVERT(VARCHAR, i.fill_factor) + '','' + @crlf ' + N'END + '
          + N''' IGNORE_DUP_KEY = '' + ' + N'CASE CONVERT(VARCHAR, i.ignore_dup_key) ' + N'WHEN 1 THEN ''ON'' '
          + N'ELSE ''OFF'' ' + N'END + '','' + @crlf + ' + N''' ALLOW_ROW_LOCKS = '' + '
          + N'CASE CONVERT(VARCHAR, i.allow_row_locks) ' + N'WHEN 1 THEN ''ON'' ' + N'ELSE ''OFF'' '
          + N'END + '','' + @crlf + ' + N''' ALLOW_PAGE_LOCKS = '' + ' + N'CASE CONVERT(VARCHAR, i.allow_page_locks) '
          + N'WHEN 1 THEN ''ON'' ' + N'ELSE ''OFF'' ' + N'END + '
          + CASE @version
                WHEN '2005' THEN
                    ''
                ELSE
                    ''','' + @crlf + ' + ''' DATA_COMPRESSION = '' + ' + '( ' + 'SELECT ' + 'CASE '
                    + 'WHEN MIN(p.data_compression_desc) =

                                          MAX(p.data_compression_desc)

                                          THEN MAX(p.data_compression_desc) '
                    + 'ELSE ''[PARTITIONS USE

                                          MULTIPLE COMPRESSION TYPES]'' '
                    + 'END ' + 'FROM sys.partitions AS p ' + 'WHERE ' + 'p.object_id = i.object_id '
                    + 'AND p.index_id = i.index_id ' + ') '
            END + N'+ @crlf + ' + N''') '' + @crlf + ' + N'''ON '' + ds.data_space + '';'' + '
          + N'@crlf + @crlf COLLATE database_default AS [-- Create Candidate Keys] ' + N'FROM sys.indexes AS i '
          + N'LEFT OUTER JOIN sys.key_constraints AS k ON ' + N'k.parent_object_id = i.object_id '
          + N'AND k.unique_index_id = i.index_id ' + N'CROSS APPLY ' + N'( ' + N'SELECT ' + N'''( '' + @crlf + '
          + N'STUFF ' + N'( ' + N'( ' + N'SELECT ' + N'( ' + N'SELECT '
          + N''','' + @crlf + '' '' + QUOTENAME(c.name) AS [text()] ' + N'FROM sys.index_columns AS ic '
          + N'JOIN sys.columns AS c ON ' + N'c.object_id = ic.object_id ' + N'AND c.column_id = ic.column_id '
          + N'WHERE ' + N'ic.object_id = i.object_id ' + N'AND ic.index_id = i.index_id ' + N'AND ic.key_ordinal > 0 '
          + N'ORDER BY ' + N'ic.key_ordinal ' + N'FOR XML PATH(''''), TYPE ' + N').value(''.'', ''VARCHAR(MAX)'') '
          + N'), ' + N'1, ' + N'3, ' + N''''' ' + N') + @crlf + ' + N''')'' ' + N') AS kc (key_columns) '
          + N'CROSS APPLY ' + N'( ' + N'SELECT ' + N'QUOTENAME(d.name) + ' + N'CASE d.type ' + N'WHEN ''PS'' THEN '
          + N'+ ' + N'''('' + ' + N'( ' + N'SELECT ' + N'QUOTENAME(c.name) ' + N'FROM sys.index_columns AS ic '
          + N'JOIN sys.columns AS c ON ' + N'c.object_id = ic.object_id ' + N'AND c.column_id = ic.column_id '
          + N'WHERE ' + N'ic.object_id = i.object_id ' + N'AND ic.index_id = i.index_id '
          + N'AND ic.partition_ordinal = 1 ' + N') + ' + N''')'' ' + N'ELSE '''' ' + N'END '
          + N'FROM sys.data_spaces AS d ' + N'WHERE ' + N'd.data_space_id = i.data_space_id '
          + N') AS ds (data_space) ' + N'WHERE ' + N'i.object_id = @object_id ' + N'AND i.is_unique = 1 ' +

    --filtered and hypothetical indexes cannot be candidate keys

    CASE @version
        WHEN '2008' THEN
            'AND i.has_filter = 0 '
        ELSE
            ''
    END        + N'AND i.is_hypothetical = 0 ' + N'AND i.is_disabled = 0 ' + N'ORDER BY ' + N'i.index_id ';



    EXEC sp_executesql @sql,
                       N'@object_id INT, @crlf CHAR(2)',
                       @object_id,
                       @crlf;



    SELECT 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.'
           + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + @crlf + CASE fk.is_not_trusted
                                                                       WHEN 0 THEN
                                                                           'WITH CHECK '
                                                                       ELSE
                                                                           'WITH NOCHECK '
                                                                   END + 'ADD '
           + CASE fk.is_system_named
                 WHEN 0 THEN
                     'CONSTRAINT ' + QUOTENAME(name) + @crlf
                 ELSE
                     ''
             END + 'FOREIGN KEY ' + @crlf + '( ' + @crlf
           + STUFF(
             (
                 SELECT
                     (
                         SELECT ',' + @crlf + ' ' + QUOTENAME(c.name) AS [text()]
                         FROM sys.foreign_key_columns AS fc
                             JOIN sys.columns AS c
                                 ON c.object_id = fc.parent_object_id
                                    AND c.column_id = fc.parent_column_id
                         WHERE fc.constraint_object_id = fk.object_id
                         ORDER BY fc.constraint_column_id
                         FOR XML PATH(''), TYPE
                     ).value('.', 'VARCHAR(MAX)')
             ),
             1,
             3,
             ''
                  ) + @crlf + ') ' + 'REFERENCES ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + '.'
           + QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) + @crlf + '( ' + @crlf
           + STUFF(
             (
                 SELECT
                     (
                         SELECT ',' + @crlf + ' ' + QUOTENAME(c.name) AS [text()]
                         FROM sys.foreign_key_columns AS fc
                             JOIN sys.columns AS c
                                 ON c.object_id = fc.referenced_object_id
                                    AND c.column_id = fc.referenced_column_id
                         WHERE fc.constraint_object_id = fk.object_id
                         ORDER BY fc.constraint_column_id
                         FOR XML PATH(''), TYPE
                     ).value('.', 'VARCHAR(MAX)')
             ),
             1,
             3,
             ''
                  ) + @crlf + ');' + @crlf + @crlf COLLATE DATABASE_DEFAULT AS [-- Create Referencing FKs]
    FROM sys.foreign_keys AS fk
    WHERE referenced_object_id = @object_id
          AND is_disabled = 0
    ORDER BY key_index_id;



END;


--Kullanımı

EXEC [dbo].[TabloKeylerininScriptiniAl] @table_name = 'Production.Product';

Prosedürü oluşturduktan sonra çalıştıralım.

Görüldüğü üzere Primary Key, Foreign Key ve Index Key alanlarının scripti oluşturulmuş oldu.

Herkese çalışma hayatında ve yaşamında başarılar kolaylıklar.