THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Script to create all foreign keys

Here's a script that generates a script to drop or create all of the existing foreign keys in a database. There are similar published scripts, but the ones I tried had bugs. If you find any errors in this script, let me know and I'll fix it. A much simpler PowerShell version is found in this post.

Comments are disabled on this post. The master copy of this script now resides on the TechNet Wiki at http://social.technet.microsoft.com/wiki/contents/articles/script-to-create-all-foreign-keys.aspx. To make corrections to or find the latest version of this script, please visit the TechNet Wiki.


DECLARE
@schema_name sysname;

DECLARE @table_name sysname;

DECLARE @constraint_name sysname;

DECLARE @constraint_object_id int;

DECLARE @referenced_object_name sysname;

DECLARE @is_disabled bit;

DECLARE @is_not_for_replication bit;

DECLARE @is_not_trusted bit;

DECLARE @delete_referential_action tinyint;

DECLARE @update_referential_action tinyint;

DECLARE @tsql nvarchar(4000);

DECLARE @tsql2 nvarchar(4000);

DECLARE @fkCol sysname;

DECLARE @pkCol sysname;

DECLARE @col1 bit;

DECLARE @action char(6);

--SET @action = 'DROP';

SET @action = 'CREATE';

DECLARE FKcursor CURSOR FOR
    select OBJECT_SCHEMA_NAME(parent_object_id)
         , OBJECT_NAME(parent_object_id), name, OBJECT_NAME(referenced_object_id)
         , object_id
         , is_disabled, is_not_for_replication, is_not_trusted
         , delete_referential_action, update_referential_action
    from sys.foreign_keys
    order by 1,2;

OPEN FKcursor;

FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name
    , @referenced_object_name, @constraint_object_id
    , @is_disabled, @is_not_for_replication, @is_not_trusted
    , @delete_referential_action, @update_referential_action;

WHILE @@FETCH_STATUS = 0

BEGIN

    IF @action <> 'CREATE'

        SET @tsql = 'ALTER TABLE '

                  + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)

                  + ' DROP CONSTRAINT ' + QUOTENAME(@constraint_name) + ';';

    ELSE

        BEGIN

        SET @tsql = 'ALTER TABLE '

                  + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)

                  + CASE @is_not_trusted

                        WHEN 0 THEN ' WITH CHECK '

                        ELSE ' WITH NOCHECK '

                    END

                  + ' ADD CONSTRAINT ' + QUOTENAME(@constraint_name)

                  + ' FOREIGN KEY ('

        SET @tsql2 = '';

        DECLARE ColumnCursor 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 = @constraint_object_id

            order by fkc.constraint_column_id;

        OPEN ColumnCursor;

        SET @col1 = 1;

        FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;

        WHILE @@FETCH_STATUS = 0

        BEGIN

            IF (@col1 = 1)

                SET @col1 = 0

            ELSE

            BEGIN

                SET @tsql = @tsql + ',';

                SET @tsql2 = @tsql2 + ',';

            END;

            SET @tsql = @tsql + QUOTENAME(@fkCol);

            SET @tsql2 = @tsql2 + QUOTENAME(@pkCol);

            FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;

        END;

        CLOSE ColumnCursor;
        DEALLOCATE ColumnCursor;

        SET @tsql = @tsql + ' ) REFERENCES ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@referenced_object_name)
                  + ' (' + @tsql2 + ')';           

        SET @tsql = @tsql
                  + ' ON UPDATE ' + CASE @update_referential_action
                                        WHEN 0 THEN 'NO ACTION '
                                        WHEN 1 THEN 'CASCADE '
                                        WHEN 2 THEN 'SET NULL '
                                        ELSE 'SET DEFAULT '
                                    END
                  + ' ON DELETE ' + CASE @delete_referential_action
                                        WHEN 0 THEN 'NO ACTION '
                                        WHEN 1 THEN 'CASCADE '
                                        WHEN 2 THEN 'SET NULL '
                                        ELSE 'SET DEFAULT '
                                    END
                  + CASE @is_not_for_replication
                        WHEN 1 THEN ' NOT FOR REPLICATION '
                        ELSE ''
                    END
                  + ';';
        END;


   
PRINT @tsql;

    IF @action = 'CREATE'
        BEGIN
        SET @tsql = 'ALTER TABLE '
                  + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)
                  + CASE @is_disabled
                        WHEN 0 THEN ' CHECK '
                        ELSE ' NOCHECK '
                    END
                  + 'CONSTRAINT ' + QUOTENAME(@constraint_name)
                  + ';';
        PRINT @tsql;
        END;

    FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name
        , @referenced_object_name, @constraint_object_id
        , @is_disabled, @is_not_for_replication, @is_not_trusted
        , @delete_referential_action, @update_referential_action;

END;

CLOSE FKcursor;
DEALLOCATE FKcursor;

Published Thursday, September 17, 2009 7:00 PM by John Paul Cook

Comments

 

cmille19 said:

Rather than using T-SQL, you might want to look at the SQL Server 2008 Powershell provider:

In SQL Server 2008 Management Studio start sqlps tables folder (right click "Start PowerShell")

To script create iterate through the list of foreign keys and call the script method:

PS SQLSERVER:\sql\Z002\SQL2K8\databases\northwind\tables> dir | foreach {$_.ForeignKeys}  | foreach {$_.Script()}

To script drop, first create a scriptingoptions object and set the scriptDrops property to true. Next, like the create  script, call the script method, but this time include the scriptoptions a parameter in the script method call:

PS SQLSERVER:\sql\Z002\SQL2K8\databases\northwind\tables> $scriptingOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions

PS SQLSERVER:\sql\Z002\SQL2K8\databases\northwind\tables> $scriptingOptions.ScriptDrops = $true

PS SQLSERVER:\sql\Z002\SQL2K8\databases\northwind\tables> dir | foreach {$_.ForeignKeys}  | foreach {$_.Script($scriptingOptions)}

September 20, 2009 11:21 AM
 

John Paul Cook said:

I wanted a solution with no SQL Server 2008 dependencies. But if SSMS 2008 can be used, PowerShell is clearly the better solution.

September 20, 2009 4:48 PM
 

pbyrne said:

I just wanted to express gratitude for you taking the time to develop and post this script.  I had to undertake a fairly large project in which I had to scramble some confidential data that was contained in database keys so that the resulting database could be used in a demo without compromising any confidential data.

This script allowed me to focus on the details of the conversion itself and appears to have worked flawlessly.

Many thanks!

Pat B.

Data Warehouse Consultant

October 7, 2009 11:20 AM
 

Dave said:

There is a bug in this script when it creates a FK constraint. It does not specify the schema of the columm it is referencing. FK's will often span schemas which doesn't appear to be supported.

Thanks for sharing this script though!

D

May 7, 2010 10:47 AM
 

John Paul Cook said:

Thanks, Dave. I made the necessary correction.

May 7, 2010 1:16 PM
 

Robert L. Smith said:

The fix for cross schema support uses the same schema regardless. You need to maintain the parent and referenced schemas. For instance

OBJECT_SCHEMA_NAME(parent_object_id) and OBJECT_SCHEMA_NAME(referenced_object_id).

August 2, 2010 11:38 AM
 

Snowwhite said:

Thanks paul! It works great. Thank You. :)

November 2, 2010 6:34 AM
 

skg said:

Thanks a lot

November 23, 2010 11:53 AM
New Comments to this post are disabled

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Opinions expressed in John's blog are strictly his own and do not represent Microsoft in any way.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement