Drop and Create all Foreign Keys The Elegant Way

Yesterday I posted a script that will drop all of the foreign keys in your database in a very inelegant, but super effective way. Today’s script is slightly more sophisticated in that you can print out the CREATE statements for the foreign keys before running the drop. The script will print out the commands rather than running them, so you can script them out and run them whenever you want.

I decided to set the granularity of the script at the schema level rather than the whole database level. It’s easy enough to alter the script to run for the whole database. In fact it’s easy enough to alter to have it run at a table level if the number of foreign keys on one table is insanely high.

I have tested and re-tested on the AdventureWorks 2012 database and am happy that it works. However if anything turns out to be inaccurate, or I’ve missed anything out, add a comment below.


 --script will not execute, but will print out commands
 --set the bits to 1 to print out commands
 --remember to print out create commands first before dropping... if you want your foreign keys back that is
 
 DECLARE @create BIT SET @create = 1
 DECLARE @drop BIT SET @drop = 1
 DECLARE @schema VARCHAR(50)

 --set the schema you want to print out for
 -- it is used ion the query below for the first cursor
 SET @schema = 'Sales'

 -- Get all existing foreign keys for schema
 DECLARE fk_cursor CURSOR
 FOR SELECT DISTINCT
 SC.Name AS SchemaName
 , SO.Name AS TableName
 , SI.Object_Id AS TableId
 , fk.[Name] AS ForeignKeyName
 , fk.object_id AS ForeignKeyId
 , OBJECT_SCHEMA_NAME (fk.referenced_object_id) as ReferencedSchemaId
 , OBJECT_NAME(fk.referenced_object_id) as ReferencedObjectName
 , fk.object_id as ConstraintObjectId
 , fk.is_not_trusted AS Trusted
 , fk.is_disabled AS [Disabled]
 , fk.is_not_for_replication as Replicated
 , fk.update_referential_action_desc as UpdateDesc
 , fk.delete_referential_action_desc as DeleteDesc
 FROM sys.indexes SI
 INNER JOIN sys.objects SO
 ON SI.object_id = SO.object_id
 INNER JOIN sys.schemas SC
 ON SO.schema_id = SC.schema_id
 INNER JOIN sys.foreign_keys fk
 ON fk.parent_object_id = so.object_id
 WHERE ObjectProperty(SI.Object_Id, 'IsUserTable') = 1
 AND fk.[Name] IS NOT NULL
 AND sc.name = @schema
 ORDER BY fk.name, fk.object_id

 DECLARE @SchemaName sysname
 DECLARE @TableName sysname
 DECLARE @TableId int
 DECLARE @FK_Name sysname
 DECLARE @FK_Id int
 DECLARE @ReferencedSchema sysname
 DECLARE @ReferencedObjectName sysname
 DECLARE @ConstraintObjectId int
 DECLARE @Trusted bit
 DECLARE @Disabled bit
 DECLARE @NotReplicated bit
 DECLARE @UpdateDesc sysname
 DECLARE @DeleteDesc sysname

 -- Loop through all foreign keys using cursor
 OPEN fk_cursor

 FETCH NEXT
 FROM fk_cursor
 INTO @SchemaName
 , @TableName
 , @TableId
 , @FK_Name
 , @FK_Id
 , @ReferencedSchema
 , @ReferencedObjectName
 , @ConstraintObjectId
 , @Trusted
 , @disabled
 , @NotReplicated
 , @UpdateDesc
 , @DeleteDesc
 WHILE (@@Fetch_Status = 0)
 BEGIN
 DECLARE @FK_Desc nvarchar(512)
 DECLARE @sCreateSql nvarchar(MAX)
 DECLARE @ReferencedColumn nvarchar (MAX) = ''
 DECLARE @sDropSql nvarchar(MAX)


 --let's make this verbose and tell us what name of foreign key on each table
 SET @FK_Desc = 'Foreign Key ' + @FK_Name + ' on table ' + @SchemaName + '.' + @TableName
 
 --check if it exists
 --if it does then delete it
 SET @sDropSql = '
--DROP
IF EXISTS 
 (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 WHERE TABLE_SCHEMA = '''+@SchemaName+'''
 AND CONSTRAINT_TYPE = ''FOREIGN KEY'' 
 AND CONSTRAINT_NAME = '''+@FK_Name 
 + ''')' + CHAR(13) +CHAR (13) + CHAR(10)
 + ' ALTER TABLE [' + @SchemaName + '].[' + @TableName 
 + '] DROP CONSTRAINT [' + @FK_Name + '] ' + CHAR(13) + CHAR(10)
 + ' GO'
 + CHAR(13) + CHAR(10)

 --check if exists
 --if it does not then create it
 SET @sCreateSql = '
--CREATE
IF NOT EXISTS
 (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 WHERE TABLE_SCHEMA = '''+@SchemaName+''' 
 AND CONSTRAINT_TYPE = ''FOREIGN KEY'' 
 AND CONSTRAINT_NAME = '''+@FK_Name 
 + ''' )' + CHAR (13) + CHAR(13) + CHAR(10)
 SET @sCreateSql = @sCreateSql + 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + ']'
 + CASE @Trusted 
 WHEN 0 THEN 
 ' WITH CHECK'+ CHAR(13) + CHAR(10)
 ELSE 
 ' WITH NOCHECK'+ CHAR(13) + CHAR(10)
 END
 SET @sCreateSql = @sCreateSql + ' ADD CONSTRAINT [' + @FK_Name + ']' + CHAR(13) + CHAR(10) 
 + ' FOREIGN KEY ('

 --add the referenced columns
 --adding a cursor within a cursor
 --getting a bit like "Inception"!

 DECLARE FK_Columns_cursor CURSOR
 FOR 
 SELECT 
 COL_NAME(fk.parent_object_id, fkc.parent_column_id)
 , COL_NAME(fk.referenced_object_id, fkc.referenced_column_id)
 FROM sys.foreign_keys fk
 inner join sys.foreign_key_columns fkc
 ON fk.object_id = fkc.constraint_object_id
 WHERE fkc.constraint_object_id = @ConstraintObjectId
 ORDER BY fkc.constraint_column_id;
 
 DECLARE @fkCol sysname
 DECLARE @pkCol sysname
 DECLARE @FKFirstColumn bit 

 OPEN FK_Columns_cursor
 SET @FKFirstColumn = 1
 FETCH NEXT
 FROM FK_Columns_cursor
 INTO @fkCol, @pkCol

 WHILE (@@Fetch_Status = 0)
 BEGIN
 IF (@FKFirstColumn = 1)
 BEGIN
 SET @FKFirstColumn = 0
 END
 ELSE
 BEGIN
 SET @sCreateSql = @sCreateSql + ',' 
 SET @ReferencedColumn = @ReferencedColumn + ',' 
 END
 
 SET @sCreateSql = @sCreateSql + ' [' + @fkCol + '] ';
 SET @ReferencedColumn = @ReferencedColumn + ' [' + @pkCol + '] ';

 FETCH NEXT
 FROM FK_Columns_cursor
 INTO @fkCol, @pkCol
 END
 -- end of column curosor
 -- when we exit this we are still in original cursor
 CLOSE FK_Columns_cursor
 DEALLOCATE FK_Columns_cursor
 
 -- add the referenced column
 -- set the action when we update/delete from FK
 SET @sCreateSql = @sCreateSql + ')' + CHAR(13) + CHAR(10)
 + ' REFERENCES ' + @ReferencedSchema + '.' + @ReferencedObjectName
 + ' (' + @ReferencedColumn + ') ' + CHAR(13) + CHAR(10)
 + ' ON UPDATE ' + REPLACE (@UpdateDesc, '_',' ') 
 + CHAR(13) + CHAR(10)
 + ' ON DELETE ' + REPLACE (@DeleteDesc, '_', ' ') 
 SET @sCreateSql = @sCreateSql + CASE @NotReplicated 
 WHEN 1 THEN 
 + ' NOT FOR REPLICATION '
 ELSE ''
 END

 --print out the command for review
 PRINT CHAR(13) + '/* && '+@FK_Desc+' && */' 
 
 IF @drop = 1
 PRINT @sDropSql
 IF @create = 1
 PRINT @sCreateSql
 PRINT 'GO' + CHAR(13) + CHAR(10)

 FETCH NEXT
 FROM fk_cursor
 INTO @SchemaName, @TableName, @TableId, @FK_Name, @FK_Id, @ReferencedSchema, @ReferencedObjectName, @ConstraintObjectId, @Trusted, @disabled, @NotReplicated, @UpdateDesc, @DeleteDesc
 END
 CLOSE fk_cursor
 DEALLOCATE fk_cursor
 --done!

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

2 thoughts on “Drop and Create all Foreign Keys The Elegant Way”

  1. just a draft variant without cursors

    SELECT
    tbl.name, ISNULL(FK.drop_stmt,N”), ISNULL(FK.create_stmt,N”)
    FROM sys.tables tbl
    INNER JOIN sys.schemas sch
    ON tbl.schema_id = sch.schema_id
    —- FOREGN KEY
    OUTER APPLY
    (
    SELECT
    ‘ALTER TABLE [‘ + sch.name + N’].[‘ + tbl.name + N’]’ + CHAR(10)
    + ‘DROP CONSTRAINT [‘+ fk.name + N’];’ + CHAR(10)

    , N’ALTER TABLE [‘ + sch.name + N’].[‘ + tbl.name + N’]’ + CHAR(10)
    + N’WITH ‘ + CASE is_not_trusted WHEN 0 THEN N’ CHECK ‘ ELSE N’ NOCHECK ‘ END + CHAR(10)
    + N’ADD CONSTRAINT [‘ + CASE is_system_named WHEN 1 THEN N’FK_’ + tbl.name ELSE + fk.name END + N’]’ + CHAR(10)
    + N’ FOREIGN KEY (‘ + STUFF(FkCol.ixColumns,1,1,N”) + N’)’ + CHAR(10)
    + N’ REFERENCES [‘ + sch.name + N’].[‘ + tbl.name + N’](‘ + STUFF(PkCol.ixColumns,1,1,N”) + N’)’ + CHAR(10)
    + N’; ‘ + CHAR(10)
    FROM sys.foreign_keys fk

    CROSS APPLY (
    SELECT
    N’,’ +
    N'[‘ + COL_NAME(fk.parent_object_id, fkc.parent_column_id) + N’]’ + CHAR(10)

    FROM sys.foreign_key_columns fkc
    WHERE fk.object_id = fkc.constraint_object_id
    ORDER BY fkc.constraint_column_id
    FOR XML PATH(”) ) FkCol (ixColumns)
    CROSS APPLY (
    SELECT
    N’,’ +
    N'[‘ + COL_NAME(fk.referenced_object_id, fkc.referenced_column_id) + N’]’ + CHAR(10)

    FROM sys.foreign_key_columns fkc
    WHERE fk.object_id = fkc.constraint_object_id
    ORDER BY fkc.constraint_column_id
    FOR XML PATH(”) ) PkCol (ixColumns)
    WHERE fk.parent_object_id = tbl.object_id
    ) AS FK(drop_stmt, create_stmt)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s