Scripting Table Key Fields in SQL Server

The process we will do is to create the script of the key fields of a table, namely the Primary Key, Foreign Key and Index Key fields.

Even though I don’t use it much, in SQL Server, in some cases, we may only need to create a script for the key fields. In addition, I think that this process, which we will do on a table basis, will save us from code confusion. It can also be useful if something like a backup of table-based key fields is to be made.

First of all, you should create the following procedure in your related database. I created the following procedure in Adventureworks database as an example.

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';

After creating the procedure, let’s run it.

As you can see, the script of the Primary Key, Foreign Key and Index Key fields has been created.

Good luck to everyone in business and life.