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….