Scripting foreign keys

Have you ever had the need to programmatically script foreign keys? If so, this script is for you. SSMS (SQL Server Management Studio) does not offer a simple solution to script foreign keys programmatically.  In most cases the process of dropping and creating foreign keys is used to expedite bulk operations, synchronize foreign key constraints between two databases, and so on.  I will provide a brief disclaimer that you should not drop all foreign key constrains, unless you really need to and understand the risks involved. Please plan appropriately to ensure the protection and integrity of your database.

I opted to use a TVF (table valued function)  for this script.  I used a TVF, so that I can pass input parameters. This function only allows the use of one input parameter, which allows the user to choose whether referential integrity should be checked before creating the indexes. The parameter is a “bit” data type and accepts a 1 or a 0.  If the supplied value is 0, RI is not enforced when creating the foreign key. Likewise, a 1 tells the logic to check and enforce RI, for the foreign key.

I will be using the AdventureWorks database to demonstrate this code.  You can find a copy of the database here: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

The code:

IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_ScriptFKs]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    DROP FUNCTION [dbo].[fn_ScriptFKs]
END
GO

/*
==============================================================================
Author: Adam Haines
------------------------------------------------------------------------------
Date: 5/1/2009
------------------------------------------------------------------------------
Inputs:
@Check_Cnsts - This input parameter is a bit type.  If the value is 0, 
    constraints are not checked in the fk create scripts.  If the value is 1,
    the db engine will check each fk before creating.  The default value is 0.
-----------------------------------------------------------------------------
Description:
-----------------------------------------------------------------------------
This inline TVF select foreign keys that meet the parameters and creates drop and
and create scripts for each foreign key.
------------------------------------------------------------------------------
Sample execution:
SELECT *
FROM [dbo].[fn_ScriptFKs](1)
WHERE
    Ref_Table_Name = 'Contact'
==============================================================================
*/
CREATE FUNCTION dbo.fn_ScriptFKs
(
    --param to check the contraints RI before creating
    @Check_Cnsts BIT = 0
)
RETURNS TABLE
RETURN(
SELECT 
    --The schema where the fk resides
    Schema_Name(t_par.Schema_Id) AS Par_Table_Schema,
    --The schema where the table resides
    t_par.name AS Par_Table_Name,
    --The schema for the table the fk references
    Schema_Name(t_ref.Schema_Id) AS Ref_Table_Schema,
    --The table the fk references
    t_ref.name AS Ref_Table_Name,
    --The index id the fk uses
    fk.[key_index_id] AS Index_Id,
    --Drop FK TSQL
    'ALTER TABLE ' + 
    QUOTENAME(SCHEMA_NAME(t_par.[schema_id])) + '.' + QUOTENAME(t_par.name) +
    ' DROP CONSTRAINT ' + QUOTENAME(fk.[name]) + ';' + CHAR(13) AS FK_Drop_Cmd,
    --Create FK TSQL
    'ALTER TABLE ' + 
    QUOTENAME(schema_name(t_par.[schema_id])) + '.' +
    QUOTENAME(t_par.[name]) +  
    CASE WHEN @Check_Cnsts = 1 
    THEN ' WITH CHECK  ' 
    ELSE ' WITH NOCHECK ' 
    END + 'ADD CONSTRAINT ' + 
    QUOTENAME(fk.[name]) + ' FOREIGN KEY (' +
    COALESCE(STUFF( 
        (SELECT ',' + QUOTENAME(COL_NAME(fkc.[parent_object_id],fkc.parent_column_id))
         FROM sys.foreign_key_columns fkc
         WHERE fk.[object_id] = fkc.[constraint_object_id]
         ORDER BY fkc.[constraint_column_id]
         FOR xml PATH(''))
    ,1,1,''),'') COLLATE DATABASE_DEFAULT + 
    ') REFERENCES ' + 
    QUOTENAME(schema_name(t_ref.[schema_id])) + '.' +
    QUOTENAME(object_name(t_ref.[object_id])) + + ' (' +
    COALESCE(STUFF( 
        (SELECT ',' + QUOTENAME(COL_NAME(fkc.[referenced_object_id],fkc.referenced_column_id)) 
         FROM sys.foreign_key_columns fkc
         WHERE fk.[object_id] = fkc.[constraint_object_id]
         ORDER BY fkc.[constraint_column_id]
         FOR xml PATH(''))
    ,1,1,''),'') COLLATE DATABASE_DEFAULT + ')' +
    CASE 
    WHEN fk.[update_referential_action] = 1 
    THEN ' ON UPDATE ' + fk.[update_referential_action_desc] 
    ELSE ''
    END +
    CASE 
    WHEN fk.[delete_referential_action] = 1 
    THEN ' ON DELETE ' + fk.[delete_referential_action_desc] 
    ELSE ''
    END +
    CASE
    WHEN fk.[is_not_for_replication] = 1
    THEN ' NOT FOR REPLICATION'
    ELSE ''
    END + ';' + CHAR(13) AS FK_Create_Cmd
FROM sys.foreign_keys fk
INNER JOIN sys.tables t_ref
    ON fk.[referenced_object_id] = t_ref.[object_id]
INNER JOIN sys.tables t_par
    ON fk.[parent_object_id] = t_par.[object_id]
WHERE 
    t_ref.[is_ms_shipped] = 0 AND 
    t_ref.[type] = 'U' AND
    t_par.[is_ms_shipped] = 0 AND 
    t_par.[type] = 'U' AND
    fk.[is_ms_shipped] = 0 AND
    fk.[type] = 'F' 
)
GO

Sample Execution:

SELECT *
FROM [dbo].[fn_ScriptFKs](1)
WHERE
    Ref_Table_Name = 'Contact'

Results:

The last couple of posts I have made are gearing us toward a goal of creating a script to move indexes to another filegroup.  By encapsulating logic we can make code easier to troubleshoot and manage.

Stay tuned….

Scripting indexes using an inline TVF

It is sometimes necessary for a person to script  indexes, so that they can synchronize indexes between databases, programmatically drop/create indexes in TSQL code, or create index scripts to the file system.  SSMS gives users multiple methods to script indexes to a file, the clip board, or to a new query window; however, SSMS does not really offer a solid method to script indexes programmatically.  I have chosen to use a TVF (Table Valued Function) because of its functionality.  TVFs allow code logic to be encapsulated in a single object like a view; however, unlike views, TVFs accept parameters. The use of parameters allow TVFs to behave like parameterized views; however, do not expect huge performance gains from query plan reuse and caching, for metadata queries. The main purpose for supplying parameters is to extend the functionality, such that it would allow me to script indexes to differing filegroups.  The results of this TVF will generate create/drop statements for all the indexes and some other data relative to the index, such as type and id. 

Now to the code:

I will be using the AdventureWorks database to demonstrate this code.  You can find a copy of the database here: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

The TVF has two input parameters.  The first parameter is @Dest_FG.  @Dest_FG accepts the filegroup that the indexes should be created on.  The second parameter is @Move_NCL_Idx_FG, which accepts the filegroup nonclustered indexes should be created on.  The nonclustered indexes can be created on a different filegroup than the clustered index or heap.

The code to create the TVF

IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_CreateIndexScripts]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    DROP FUNCTION [dbo].[fn_CreateIndexScripts]
END
GO

/*
==============================================================================
Author: Adam Haines
------------------------------------------------------------------------------
Date: 5/1/2009
------------------------------------------------------------------------------
Inputs:
@Dest_FG - This is the filegroup we should create the index on.  The default
    value for this parameter is the Primary filegroup.
@Move_NCL_Idx_FG - Use this to move nonclustered indexes to a different
    filgroup than the clustered index/heap. If this value is null, the non
    clustered indexes are created in the same filegroup as the clustered
    index/heap.
-----------------------------------------------------------------------------
Description:
-----------------------------------------------------------------------------
This inline TVF select indexes that meet the parameters and creates drop and
and create scripts for each returned row.
------------------------------------------------------------------------------
Sample execution:
SELECT *
FROM [dbo].[usp_MoveTablesToNewFG]('PRIMARY','Test_FG',NULL)
==============================================================================
*/

CREATE FUNCTION dbo.fn_CreateIndexScripts
(
    @Dest_FG VARCHAR(50) = 'PRIMARY',
    @Move_NCL_Idx_FG VARCHAR(50) = NULL
)
RETURNS TABLE
RETURN
(
SELECT
    SCHEMA_NAME(t.schema_id) AS [SchemaName],
    t.[name] AS [TableName],
    FILEGROUP_NAME(i.[data_space_id]) AS [FilegroupName],
    i.[index_id] AS [IdxId],
    i.[type] AS [IndexType],
    --************* CREATE "CREATE" INDEX SCRIPT ******************
    CASE WHEN i.[type] = 3 THEN --0=heap,1=clustered,2=nonclustered,3=xml,4=spatial
        'CREATE ' +
            CASE WHEN xi.secondary_type IS NULL --when no secondary type is present we know it is a primary index
            THEN 'PRIMARY '
            ELSE ''
            END    + 'XML INDEX ' +
            QUOTENAME(xi.[name]) + ' ON ' +
            QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +
            QUOTENAME(t.[name]) +
            '(' + QUOTENAME(COL_NAME(t.[object_id],ic.column_id)) + ')' +
            CASE WHEN xi.secondary_type IS NULL 
            THEN '' --when no secondary type is present we know it is a primary index and do need a using clause
            ELSE ' USING XML INDEX ' + QUOTENAME(PRI.name) +
                 ' FOR '+ 
                CASE xi.secondary_type
                    WHEN 'V' THEN 'VALUE'
                    WHEN 'P' THEN 'PATH'
                    WHEN 'R' THEN 'PROPERTY'
                ELSE '' 
                END
            END + ' WITH (Pad_Index = ' +
                CASE WHEN xi.[is_padded] = 1 
                THEN 'ON);'
                ELSE 'OFF);'
                END
    ELSE
        CASE WHEN i.is_primary_key = 1 THEN
            'ALTER TABLE ' + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) +
            ' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(i.[name]) +
            ' PRIMARY KEY ' + COALESCE(i.[type_desc],'') + '(' +
            COALESCE(STUFF( 
                (SELECT ',' + QUOTENAME(c.[name]) + 
                        CASE WHEN ic.[is_descending_key] = 1 
                        THEN ' DESC'
                        ELSE ' ASC' 
                        END
                 FROM sys.index_columns ic
                 INNER JOIN sys.columns c
                    ON  ic.[column_id] = c.[column_id] AND    
                        ic.[object_id] = c.[object_id]
                 WHERE i.[index_id] = ic.[index_id] AND
                       i.[object_id] = ic.[object_id] AND
                       ic.[is_included_column] = 0
                 ORDER BY ic.[key_ordinal]
                 FOR xml PATH(''))
            ,1,1,''),'') COLLATE DATABASE_DEFAULT + ')' +
            CASE WHEN EXISTS (SELECT 1 
                              FROM sys.[index_columns] ic2 
                              WHERE ic2.[index_id] = i.[index_id] AND 
                                    ic2.[object_id] = i.[object_id] AND
                                    ic2.[is_included_column] = 1)
            THEN ' INCLUDE (' +
                 COALESCE(STUFF( 
                    (SELECT ',' + QUOTENAME(c.[name])
                     FROM sys.index_columns ic
                     INNER JOIN sys.columns c
                        ON  ic.[column_id] = c.[column_id] AND    
                            ic.[object_id] = c.[object_id]
                     WHERE i.[index_id] = ic.[index_id] AND
                           i.[object_id] = ic.[object_id] AND
                           ic.[is_included_column] = 1
                     ORDER BY ic.[key_ordinal]
                     FOR xml PATH(''))
                 ,1,1,''),'') COLLATE DATABASE_DEFAULT 
            ELSE ''
            END +
            CASE WHEN i.is_padded = 1 THEN 
                ' WITH PAD INDEX ON ' + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            ELSE ' ON ' + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            END
        WHEN i.is_unique_constraint = 1 THEN
            'ALTER TABLE ' + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) +
            ' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(i.[name]) +
            ' UNIQUE ' + i.[type_desc] + ' (' +
            COALESCE(STUFF( 
                (SELECT ',' + QUOTENAME(c.[name]) + 
                        CASE WHEN ic.[is_descending_key] = 1 
                        THEN ' DESC'
                        ELSE ' ASC' 
                        END
                 FROM sys.index_columns ic
                 INNER JOIN sys.columns c
                    ON  ic.[column_id] = c.[column_id] AND    
                        ic.[object_id] = c.[object_id]
                 WHERE i.[index_id] = ic.[index_id] AND
                       i.[object_id] = ic.[object_id] AND
                       ic.[is_included_column] = 0
                 ORDER BY ic.[key_ordinal]
                 FOR xml PATH(''))
            ,1,1,''),'') COLLATE DATABASE_DEFAULT + ')' +
            CASE WHEN EXISTS (SELECT 1 
                              FROM sys.[index_columns] ic2 
                              WHERE ic2.[index_id] = i.[index_id] AND 
                                    ic2.[object_id] = i.[object_id] AND
                                    ic2.[is_included_column] = 1)
            THEN ' INCLUDE (' +
                 COALESCE(STUFF( 
                    (SELECT ',' + QUOTENAME(c.[name])
                     FROM sys.index_columns ic
                     INNER JOIN sys.columns c
                        ON  ic.[column_id] = c.[column_id] AND    
                            ic.[object_id] = c.[object_id]
                     WHERE i.[index_id] = ic.[index_id] AND
                           i.[object_id] = ic.[object_id] AND
                           ic.[is_included_column] = 1
                     ORDER BY ic.[key_ordinal]
                     FOR xml PATH(''))
                 ,1,1,''),'') COLLATE DATABASE_DEFAULT 
            ELSE ''
            END  +
            CASE WHEN i.is_padded = 1 THEN 
                ' WITH PAD INDEX ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            ELSE ' ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            END
        WHEN i.is_unique = 1 THEN
            'CREATE UNIQUE ' + i.[type_desc] + ' INDEX ' + 
            QUOTENAME(i.[name]) + 
            ' ON ' + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) + ' (' +
            COALESCE(STUFF( 
                (SELECT ',' + QUOTENAME(c.[name]) + 
                        CASE WHEN ic.[is_descending_key] = 1 
                        THEN ' DESC'
                        ELSE ' ASC' 
                        END
                 FROM sys.index_columns ic
                 INNER JOIN sys.columns c
                    ON  ic.[column_id] = c.[column_id] AND    
                        ic.[object_id] = c.[object_id]
                 WHERE i.[index_id] = ic.[index_id] AND
                       i.[object_id] = ic.[object_id] AND
                       ic.[is_included_column] = 0
                 ORDER BY ic.[key_ordinal]
                 FOR xml PATH(''))
            ,1,1,''),'') COLLATE DATABASE_DEFAULT + ')' +
            CASE WHEN EXISTS (SELECT 1 
                              FROM sys.[index_columns] ic2 
                              WHERE ic2.[index_id] = i.[index_id] AND 
                                    ic2.[object_id] = i.[object_id] AND
                                    ic2.[is_included_column] = 1)
            THEN ' INCLUDE (' +
                 COALESCE(STUFF( 
                    (SELECT ',' + QUOTENAME(c.[name])
                     FROM sys.index_columns ic
                     INNER JOIN sys.columns c
                        ON  ic.[column_id] = c.[column_id] AND    
                            ic.[object_id] = c.[object_id]
                     WHERE i.[index_id] = ic.[index_id] AND
                           i.[object_id] = ic.[object_id] AND
                           ic.[is_included_column] = 1
                     ORDER BY ic.[key_ordinal]
                     FOR xml PATH(''))
                 ,1,1,''),'') COLLATE DATABASE_DEFAULT + ');'
            ELSE ''
            END +
            CASE WHEN i.is_padded = 1 THEN 
                ' WITH PAD INDEX ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            ELSE ' ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            END
        ELSE
            'CREATE ' + i.[type_desc] + ' INDEX ' + 
            QUOTENAME(i.[name]) + 
            ' ON ' + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) + ' (' +
            COALESCE(STUFF( 
                (SELECT ',' + QUOTENAME(c.[name]) + 
                        CASE WHEN ic.[is_descending_key] = 1 
                        THEN ' DESC'
                        ELSE ' ASC' 
                        END
                 FROM sys.index_columns ic
                 INNER JOIN sys.columns c
                    ON  ic.[column_id] = c.[column_id] AND    
                        ic.[object_id] = c.[object_id]
                 WHERE i.[index_id] = ic.[index_id] AND
                       i.[object_id] = ic.[object_id] AND
                       ic.[is_included_column] = 0
                 ORDER BY ic.[key_ordinal]
                 FOR xml PATH(''))
            ,1,1,''),'') COLLATE DATABASE_DEFAULT + ')' +
            CASE WHEN EXISTS (SELECT 1 
                              FROM sys.[index_columns] ic2 
                              WHERE ic2.[index_id] = i.[index_id] AND 
                                    ic2.[object_id] = i.[object_id] AND
                                    ic2.[is_included_column] = 1)
            THEN ' INCLUDE (' +
                 COALESCE(STUFF( 
                    (SELECT ',' + QUOTENAME(c.[name])
                     FROM sys.index_columns ic
                     INNER JOIN sys.columns c
                        ON  ic.[column_id] = c.[column_id] AND    
                            ic.[object_id] = c.[object_id]
                     WHERE i.[index_id] = ic.[index_id] AND
                           i.[object_id] = ic.[object_id] AND
                           ic.[is_included_column] = 1
                     ORDER BY ic.[key_ordinal]
                     FOR xml PATH(''))
                 ,1,1,''),'') COLLATE DATABASE_DEFAULT + ');'
            ELSE ''
            END +
            CASE WHEN i.is_padded = 1 THEN 
                ' WITH PAD INDEX ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            ELSE ' ON '  + 
                CASE WHEN @Move_NCL_Idx_FG IS NOT NULL AND i.[type] = 2
                THEN QUOTENAME(@Move_NCL_Idx_FG) + ';'
                ELSE QUOTENAME(@Dest_FG) + ';'
                END
            END
        END 
    END AS CreateIdxScript,
    --************* CREATE "DROP" XML INDEX SCRIPT ******************
    CASE WHEN i.[type] = 3 THEN
        'DROP INDEX ' + QUOTENAME(xi.Name) + ' ON ' +
        QUOTENAME(SCHEMA_NAME(t.schema_id)) +     '.' +
        QUOTENAME(t.name)
    ELSE
        CASE WHEN i.[is_primary_key] = 1 OR i.[is_unique_constraint] = 1 THEN
            'ALTER TABLE ' + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) +
            ' DROP CONSTRAINT ' + QUOTENAME(i.[name])  + ';'
        ELSE
            'DROP INDEX ' + QUOTENAME(i.[name]) + ' ON ' +
            + QUOTENAME(Schema_Name(t.[schema_id])) + '.' + QUOTENAME(t.[name]) + ';'
        END
    END AS DropIdxScript,
    CASE 
    WHEN i.[type] IN(0,1) THEN 1
    WHEN i.[type] = 2 THEN 2
    WHEN i.[type] = 3 THEN
        CASE WHEN xi.[secondary_type] IS NULL 
        THEN 3 --Primary XML index
        ELSE 4 --Secondary XML index
        END
    --WHEN i.[type] = 4 THEN 5 --I am not generating spatial indexes
    ELSE 6
    END AS [CreateSeq],
    CASE 
    WHEN i.[type] IN(0,1) THEN 6
    WHEN i.[type] = 2 THEN 5
    WHEN i.[type] = 3 THEN
        CASE WHEN xi.[secondary_type] IS NULL 
        THEN 4 --Primary XML index
        ELSE 3 --Secondary XML index
        END
    --WHEN i.[type] = 4 THEN 2 --I am not generating spatial indexes
    ELSE 1
    END AS [DropSeq]
FROM sys.tables t
INNER JOIN sys.[indexes] i
    ON t.[object_id] = i.[object_id]
LEFT JOIN sys.[xml_indexes] xi
    ON xi.[object_id] = i.[object_id] AND
       xi.[index_id] = i.[index_id]
LEFT JOIN sys.xml_indexes AS PRI --primary XML Index info
    ON xi.object_id = PRI.object_id AND
       xi.using_xml_index_id = PRI.index_id
LEFT JOIN sys.[index_columns] ic
    ON xi.object_id = ic.Object_ID AND
       xi.Index_id = ic.Index_id
WHERE
    t.[is_ms_shipped] = 0 AND 
    t.[type] = 'U' 
GROUP BY
    t.[object_id],
    t.[name],
    t.[schema_id],
    i.[name],
    i.[type_desc],
    i.[object_id],
    i.[index_id],
    i.[is_primary_key],
    i.[is_padded],
    i.[is_unique],
    i.[is_unique_constraint],
    i.[is_hypothetical],
    i.[is_disabled],
    i.[type],
    i.[data_space_id],
    xi.[name],
    xi.[secondary_type],
    xi.[is_padded],
    ic.[column_id],
    Pri.[name]
)
GO

Sample Execution:

SELECT *
FROM [dbo].[fn_CreateIndexScripts](DEFAULT,DEFAULT)
WHERE
    IndexType IN(1,2,3)

Results:

There you go. A script that will generate indexes in the same or differing filegroups.  There are a lot of different uses for a script like this. I have a script in mind that will use this code to move tables and indexes to a different filegroup programmatically. 

More to come.. stay tuned.