Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Drop and Re-Create All Foreign Key Constraints in SQL Server


By:   |   Read Comments (24)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Constraints

Attend these FREE MSSQLTips webcasts >> click to register


Problem

You may have been in a scenario where you needed to quickly generate a script to drop and then subsequently re-create all of the foreign keys in a database (or in a specific schema, or matching a specific naming scheme). In some situations you can simply disable and re-enable the constraints, which isn't all that complex at all. In other cases (say, you want to truncate all tables), you actually need to drop and re-create the constraints.

Regardless of the underlying purpose, this is rather tedious to do in Management Studio, since there is no top-level "Foreign Keys" node in the Object Explorer tree - otherwise you could just select multiple items in Object Explorer Details, right-click, and be on your way.

When you start thinking about how to solve this problem, and there are existing tips that do offer solutions already, your first thought is probably: "I'll just use a cursor against sys.foreign_keys and build the scripts dynamically!" Then you realize that some of your foreign key constraints are comprised of more than one column - certainly an often and understandably unforeseen complication. This definitely throws a wrench in your plans, as now it's a nested cursor: one to loop through all the constraints, and then for each constraint, a loop for the 1-n columns referenced.

Solution

I have what I think is a better way than trying to write convoluted and nested cursors, and no, it doesn't involve PowerShell. (That's not saying PowerShell is a bad approach for this kind of problem, and I invite you to share your solutions from that angle. I'm just trying to stay within the database here.)

I've recently blogged about the FOR XML PATH() approach to grouped concatenation (see here and here), but I didn't really get into any real, practical solutions, like this one, in those posts.

I have grown quite fond of using this method to solve problems like this, where I can eliminate tedious and repetitive cursor code and/or while loops. Note that this shift is not in the name of performance - after all, in most cases, it is unimportant whether this specific task is accomplished in 8.7 seconds or 11.2 seconds. It doesn't end up being any simpler either, really, but it sure is less boring to come up with a working solution that covers all edge cases.

The code below generates two separate sets of commands: one to drop all foreign key constraints, and one to create them again. These scripts are stored in a table so that, if you drop the constraints and then disaster of some kind strikes during the create, you still have everything handy and can troubleshoot if needed - including extracting the scripts for all the constraints that haven't yet run, but aren't causing any issues otherwise.

CREATE TABLE #x -- feel free to use a permanent table
(
  drop_script NVARCHAR(MAX),
  create_script NVARCHAR(MAX)
);
  
DECLARE @drop   NVARCHAR(MAX) = N'',
        @create NVARCHAR(MAX) = N'';

-- drop is easy, just build a simple concatenated list from sys.foreign_keys:
SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
    + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id];

INSERT #x(drop_script) SELECT @drop;

-- create is a little more complex. We need to generate the list of 
-- columns on both sides of the constraint, even though in most cases
-- there is only one column.
SELECT @create += N'
ALTER TABLE ' 
   + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
   + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) 
   + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
   -- get all the columns in the constraint table
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.parent_column_id = c.column_id
    AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
  + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
  + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
   -- get all the referenced columns
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.referenced_column_id = c.column_id
    AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
  ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs 
  ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;

UPDATE #x SET create_script = @create;

PRINT @drop;
PRINT @create;

/*
EXEC sp_executesql @drop
-- clear out data etc. here
EXEC sp_executesql @create;
*/

Conclusion

I will be the first to admit: the script is a lot to digest. However, before trying to completely reverse engineer all of the logic on first glance, I urge you to try this code (with the EXEC lines still commented out of course) in your hairiest, most complex schemas. Please let me know if you have a scenario where you find any discrepancies in the comments section below.

Next Steps


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, September 14, 2017 - 8:24:13 AM - Kleidi Kumbaro Back To Top

Based on Gauthier Segay code and the comments of other users like me here is a modified script that will include the referentail integrity options on update and on delete as well as with check and not for replication. Keep in mind that this is tested by my only for foreign keys and not on constraints. Thanks to the original poster for the idea

with
 unique_constraint_infos (schemaname, tablename, constraintname, columnname)
 as (
  select
   quotename(tc.table_schema)
   , quotename(tc.table_name)
   , quotename(tc.constraint_name)
   , quotename(cc.column_name)
  from
   information_schema.table_constraints tc
   inner join information_schema.constraint_column_usage cc on tc.constraint_name = cc.constraint_name
  where
   lower(tc.constraint_type) = 'unique'
 )
 , check_constraint_infos (schemaname, tablename, constraintname, definition)
 as (
  select
   quotename(cs.name)
   , quotename(ct.name)
   , quotename(ck.name)
   , ck.definition
  from
   sys.check_constraints ck
   inner join sys.tables ct on ck.parent_object_id = ct.[object_id]
   inner join sys.schemas cs on ct.[schema_id] = cs.[schema_id] 
 )
 , foreign_key_infos (constraintschemaname, constrainttablename, referenceschemaname, referencetablename, constraintname, constraintcolumns, referencecolumns, delOption, upOption, checkOption, refOption)
 as (
  select
   quotename(cs.name)
   , quotename(ct.name)
   , quotename(rs.name)
   , quotename(rt.name)
   , quotename(fk.name)
   , stuff(
    (select
     ',' + quotename(c.name)
     -- get all the columns in the constraint table
    from
     sys.columns as c
    inner join sys.foreign_key_columns as fkc
     on fkc.parent_column_id = c.column_id
     and fkc.parent_object_id = c.[object_id]
    where
     fkc.constraint_object_id = fk.[object_id]
    for xml path(''), type
    ).value('.[1]', 'nvarchar(max)')
   , 1, 1, ''
   )
   , stuff(
    (select
     ',' + quotename(c.name)
     -- get all the referenced columns
    from
     sys.columns as c
     inner join sys.foreign_key_columns as fkc
      on fkc.referenced_column_id = c.column_id
      and fkc.referenced_object_id = c.[object_id]
     where fkc.constraint_object_id = fk.[object_id]
     for xml path(''), type
     ).value('.[1]', N'nvarchar(max)')
   , 1, 1, '')
   , case fk.delete_referential_action when 0 then 'No Action' when 1 then 'Cascade' when 2 then 'Set Null' when 3 then 'Set Default' END
   ,case fk.update_referential_action when 0 then 'No Action' when 1 then 'Cascade' when 2 then 'Set Null' when 3 then 'Set Default' END
   ,case fk.is_not_trusted when 0 then 'WITH CHECK' ELSE 'WITH NOCHECK' END
   ,case fk.is_not_for_replication when 1 then 'NOT FOR REPLICATION' ELSE '' END

  from
   sys.foreign_keys as fk
   inner join sys.tables as rt on fk.referenced_object_id = rt.[object_id]
   inner join sys.schemas as rs on rt.[schema_id] = rs.[schema_id]
  inner join sys.tables as ct on fk.parent_object_id = ct.[object_id]
  inner join sys.schemas as cs on ct.[schema_id] = cs.[schema_id]
  where
   rt.is_ms_shipped = 0 and ct.is_ms_shipped = 0
   --AND fk.is_not_trusted = 1
 )
-- create/drop foreign keys
select distinct
 'foreign keys' script_type
 ,
  ' alter table ' + fki.constraintschemaname + '.' + fki.constrainttablename
  + ' ' + fki.checkOption
  + ' add constraint ' + fki.constraintname
  + ' foreign key (' + fki.constraintcolumns + ')'
  + ' references ' + fki.referenceschemaname + '.' + fki.referencetablename
  + ' ('  + fki.referencecolumns + ') ON DELETE ' + fki.delOption + ' ON UPDATE ' + fki.upOption
  + ' ' + fki.refOption + ';'  create_script
 ,
  'alter table ' + fki.constraintschemaname + '.' + fki.constrainttablename
  + ' drop constraint ' + fki.constraintname + ';' drop_script
from
 foreign_key_infos fki 
union all
-- create/drop unique constraints
select distinct
 'unique constraints'
 ,
  ' alter table ' + uci.schemaname + '.' + uci.tablename
  + ' add constraint ' + uci.constraintname
  + ' unique ('
  + stuff(
  (
   select ', ' + ci.columnname
   from unique_constraint_infos ci
   where ci.schemaname = uci.schemaname
    and ci.tablename = uci.tablename
    and ci.constraintname = uci.constraintname
   for xml path('')
  ), 1, 1, '')
  + ');'
 ,
  ' alter table ' + uci.schemaname + '.' + uci.tablename
  + ' drop constraint ' + uci.constraintname + ';'
from
 unique_constraint_infos uci
union all
-- create/drop check constraints
select distinct
 'check constraints'
 ,
  'alter table ' + cki.schemaname + '.' + cki.tablename
  + ' with check add constraint ' + cki.constraintname
  + ' check ' + cki.definition + ';'
 ,
  ' alter table ' + cki.schemaname + '.' + cki.tablename
  + ' drop constraint ' + cki.constraintname + ';'
from
 check_constraint_infos cki


Monday, June 19, 2017 - 4:22:43 PM - Gabe Back To Top

Nice script! Something I added on my own was to add functionality around on update/delete options and so-forth (e.g. ON UPDATE CASCADE, ON DELETE SET NULL, etc).. Those can be pulled using objectproperty on the constraint/foreign key's object id. e.g.

select
    CurrentDeleteAction = case objectproperty(object_id, 'CnstDeleteAction') when 0 then 'No Action' when 1 then 'Cascade' when 2 then 'Set Null' when 3 then 'Set Default' end,
    CurrentUpdateAction = case objectproperty(object_id, 'CnstUpdateAction') when 0 then 'No Action' when 1 then 'Cascade' when 2 then 'Set Null' when 3 then 'Set Default' end,
    ForeignKeyName = name,
    ParentTable = object_name(parent_object_id)
from sys.foreign_keys


Wednesday, May 31, 2017 - 3:25:07 AM - LR Back To Top

 Great script thanks!!


Thursday, May 18, 2017 - 10:36:38 PM - Michael Back To Top

 Loved this script! Thank you.

 


Thursday, April 27, 2017 - 3:01:00 PM - Chris Prosser Back To Top

This saved me a ton of time. Thank you.


Tuesday, April 18, 2017 - 12:05:24 PM - Janelle Back To Top

 Great article.  Saved me a ton of time.   

 

I turned the code into a function so I could the function and return the 2 scripts for a specific table.  It returns them for fk constraints on the table and for fk constraints referencing the table.   

 

CREATE FUNCTION [dbo].[GetForeignKeyInformation] (@tableName NVARCHAR(50))  

RETURNS @fkTemp TABLE ( drop_script VARCHAR(MAX), create_script VARCHAR(MAX) )

AS  

BEGIN

   

DECLARE @drop   NVARCHAR(MAX) = N'',

       @create NVARCHAR(MAX) = N''

 

-- drop is easy, just build a simple concatenated list from sys.foreign_keys:

SELECT @drop += N'

ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 

+ ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'

FROM sys.foreign_keys AS fk

INNER JOIN sys.tables AS ct

 ON fk.parent_object_id = ct.[object_id]

INNER JOIN sys.schemas AS cs 

 ON ct.[schema_id] = cs.[schema_id]

INNER JOIN sys.tables AS rt

 ON fk.referenced_object_id = rt.object_id 

WHERE ct.name = @tableName

OR rt.name = @tableName

 

SELECT @create += N'

ALTER TABLE ' 

  + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 

  + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) 

  + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)

  -- get all the columns in the constraint table

FROM sys.columns AS c 

INNER JOIN sys.foreign_key_columns AS fkc 

ON fkc.parent_column_id = c.column_id

AND fkc.parent_object_id = c.[object_id]

WHERE fkc.constraint_object_id = fk.[object_id]

ORDER BY fkc.constraint_column_id 

FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')

 + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)

 + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)

  -- get all the referenced columns

FROM sys.columns AS c 

INNER JOIN sys.foreign_key_columns AS fkc 

ON fkc.referenced_column_id = c.column_id

AND fkc.referenced_object_id = c.[object_id]

WHERE fkc.constraint_object_id = fk.[object_id]

ORDER BY fkc.constraint_column_id 

FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') 

+ CASE

WHEN fk.delete_referential_action_desc = 'CASCADE' THEN N') ON DELETE CASCADE;'

ELSE N');'

END 

FROM sys.foreign_keys AS fk

INNER JOIN sys.tables AS rt -- referenced table

ON fk.referenced_object_id = rt.[object_id]

INNER JOIN sys.schemas AS rs 

ON rt.[schema_id] = rs.[schema_id]

INNER JOIN sys.tables AS ct -- constraint table

ON fk.parent_object_id = ct.[object_id]

INNER JOIN sys.schemas AS cs 

ON ct.[schema_id] = cs.[schema_id]

WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0

AND (rt.name = @tableName OR ct.name = @tableName)

 

INSERT INTO @fkTemp 

SELECT @drop, @create

RETURN

END

 


Friday, September 23, 2016 - 5:23:52 AM - Eric Pitrel Back To Top

Hello,

When you create the script part of @create : 

it seems that you have missed the enforcing data integrity

like this ("CASE fk.is_not_trusted") :

SELECT @create += N'

ALTER TABLE ' 

   + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 

+ CASE fk.is_not_trusted

WHEN 0 THEN ' WITH CHECK '

ELSE ' WITH NOCHECK '

END

   + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) 

   + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)

   -- get all the columns in the constraint table

    FROM sys.columns AS c 

    INNER JOIN sys.foreign_key_columns AS fkc 

    ON fkc.parent_column_id = c.column_id

    AND fkc.parent_object_id = c.[object_id]

    WHERE fkc.constraint_object_id = fk.[object_id]

    ORDER BY fkc.constraint_column_id 

    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')

  + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)

  + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)

   -- get all the referenced columns

    FROM sys.columns AS c 

    INNER JOIN sys.foreign_key_columns AS fkc 

    ON fkc.referenced_column_id = c.column_id

    AND fkc.referenced_object_id = c.[object_id]

    WHERE fkc.constraint_object_id = fk.[object_id]

    ORDER BY fkc.constraint_column_id 

    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'

FROM sys.foreign_keys AS fk

INNER JOIN sys.tables AS rt -- referenced table

  ON fk.referenced_object_id = rt.[object_id]

INNER JOIN sys.schemas AS rs 

  ON rt.[schema_id] = rs.[schema_id]

INNER JOIN sys.tables AS ct -- constraint table

  ON fk.parent_object_id = ct.[object_id]

INNER JOIN sys.schemas AS cs 

  ON ct.[schema_id] = cs.[schema_id]

WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;

 

 

BR

Eric

 

 


Friday, May 13, 2016 - 2:51:00 AM - Steve Back To Top

 

Hi

 

Just wanted to say.. great post .Saved me a lot of work today.

 

Thanks

 

Steve


Thursday, August 20, 2015 - 12:21:19 PM - Mitch Kirsch Back To Top

Hi Aaron, great script!  The only modification I had to made was to add WHERE ct.is_ms_shipped = 0 to the end of the SELECT that generates the contents of @drop, since the SELECT that generates the contents of @create ends with WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0  (before my modification, I was dropping a foreign key set up by merge replication but then not re-adding it later).


Tuesday, March 24, 2015 - 11:33:57 PM - kakugiki Back To Top

This is very helpful! Thank you Aaron!

@Sumit Rastogi: I use it for a database-wide conversion of char/varchar to nchar/nvarchar.


Thursday, March 12, 2015 - 8:55:24 AM - Ramesh Back To Top

Aaron, This article was very helpful. Thanks a lot.

 


Friday, February 27, 2015 - 8:27:33 AM - Gauthier Segay Back To Top

Aaron and Carl, thanks so much for your contribution.

I figured out that we are still missing unique constraints, so I went on and added that and also refactored the whole thing to make it tidy and more maintainable, using CTE preparing all relevant info in a nice format so that don't mix the string building logic with how to obtain the data from system tables.

The statement returns a table with a script type (for the type of constraint), a create script column and a drop script column.

In case I find issues, I'll update this https://gist.github.com/smoothdeveloper/ea48e43aead426248c0f

I hope this helps.

-------------------------------

with
    unique_constraint_infos (schemaname, tablename, constraintname, columnname)
    as (
        select
            quotename(tc.table_schema)
            , quotename(tc.table_name)
            , quotename(tc.constraint_name)
            , quotename(cc.column_name)
        from
            information_schema.table_constraints tc
            inner join information_schema.constraint_column_usage cc on tc.constraint_name = cc.constraint_name
        where
            lower(tc.constraint_type) = 'unique'
    )
    , check_constraint_infos (schemaname, tablename, constraintname, definition)
    as (
        select
            quotename(cs.name)
            , quotename(ct.name)
            , quotename(ck.name)
            , ck.definition
        from
            sys.check_constraints ck
            inner join sys.tables ct on ck.parent_object_id = ct.[object_id]
            inner join sys.schemas cs on ct.[schema_id] = cs.[schema_id]   
    )
    , foreign_key_infos (constraintschemaname, constrainttablename, referenceschemaname, referencetablename, constraintname, constraintcolumns, referencecolumns)
    as (
        select
            quotename(cs.name)
            , quotename(ct.name)
            , quotename(rs.name)
            , quotename(rt.name)
            , quotename(fk.name)
            , stuff(
                (select
                    ',' + quotename(c.name)
                    -- get all the columns in the constraint table
                from
                    sys.columns as c
                inner join sys.foreign_key_columns as fkc
                    on fkc.parent_column_id = c.column_id
                    and fkc.parent_object_id = c.[object_id]
                where
                    fkc.constraint_object_id = fk.[object_id]
                for xml path(''), type
                ).value('.[1]', 'nvarchar(max)')
            , 1, 1, ''
            )
            , stuff(
                (select
                    ',' + quotename(c.name)
                    -- get all the referenced columns
                from
                    sys.columns as c
                    inner join sys.foreign_key_columns as fkc
                        on fkc.referenced_column_id = c.column_id
                        and fkc.referenced_object_id = c.[object_id]
                    where fkc.constraint_object_id = fk.[object_id]
                    for xml path(''), type
                    ).value('.[1]', N'nvarchar(max)')
            , 1, 1, '')
        from
            sys.foreign_keys as fk
            inner join sys.tables as rt on fk.referenced_object_id = rt.[object_id]
            inner join sys.schemas as rs on rt.[schema_id] = rs.[schema_id]
        inner join sys.tables as ct on fk.parent_object_id = ct.[object_id]
        inner join sys.schemas as cs on ct.[schema_id] = cs.[schema_id]
        where
            rt.is_ms_shipped = 0 and ct.is_ms_shipped = 0
    )
-- create/drop foreign keys
select distinct
    'foreign keys' script_type
    ,
        ' alter table ' + fki.constraintschemaname + '.' + fki.constrainttablename
        + ' add constraint ' + fki.constraintname
        + ' foreign key (' + fki.constraintcolumns + ')'
        + ' references ' + fki.referenceschemaname + '.' + fki.referencetablename
        + ' ('  + fki.referencecolumns + ');' create_script
    ,
        'alter table ' + fki.constraintschemaname + '.' + fki.constrainttablename
        + 'drop constraint ' + fki.constraintname + ';' drop_script
from
    foreign_key_infos fki   
union all
-- create/drop unique constraints
select distinct
    'unique constraints'
    ,
        ' alter table ' + uci.schemaname + '.' + uci.tablename
        + ' add constraint ' + uci.constraintname
        + ' unique ('
        + stuff(
        (
            select ', ' + ci.columnname
            from unique_constraint_infos ci
            where ci.schemaname = uci.schemaname
                and ci.tablename = uci.tablename
                and ci.constraintname = uci.constraintname
            for xml path('')
        ), 1, 1, '')
        + ');'
    ,
        ' alter table ' + uci.schemaname + '.' + uci.tablename
        + ' drop constraint ' + uci.constraintname + ';'
from
    unique_constraint_infos uci
union all
-- create/drop check constraints
select distinct
    'check constraints'
    ,
        'alter table ' + cki.schemaname + '.' + cki.tablename
        + ' with check add constraint ' + cki.constraintname
        + ' check ' + cki.definition + ';'
    ,
        ' alter table ' + cki.schemaname + '.' + cki.tablename
        + ' drop constraint ' + cki.constraintname + ';'
from
    check_constraint_infos cki

-------------------------------


Wednesday, January 14, 2015 - 7:17:45 AM - Aaron Bertrand Back To Top

Yeti, please see this tip

http://www.mssqltips.com/sqlservertip/3185/validate-the-contents-of-large-dynamic-sql-strings-in-sql-server


Wednesday, January 14, 2015 - 7:14:56 AM - Aaron Bertrand Back To Top

Yeti, I suspect you've been fooled by the limitations of the PRINT command, which can only output 8K. That is just there for spot checking, and isn't what you should be using to execute the commands. E98


Wednesday, January 14, 2015 - 4:04:43 AM - yeti Back To Top

Unfortunately your script only generated create script for 25 out of my 75 foreign keys. The below one generated all of them. Don't know the difference and now have no time to dig into it, just letting you know. Best regards.

http://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx

 


Saturday, January 03, 2015 - 6:06:40 AM - alzdba Back To Top

As "requested" a Powershell version to accomplish this functionality (my first little MSSQLTips article ) : http://www.mssqltips.com/sqlservertip/3424/drop-and-recreate-all-foreign-key-constraints-in-a-sql-server-database-using-powershell/

 

Cheers,

Johan


Tuesday, November 04, 2014 - 1:43:48 AM - Sumit Rastogi Back To Top

Hi Aaron,

The article is very good, but I would like to know, what are the cases in which we use it.

Thanks,

Sumit


Friday, October 17, 2014 - 2:15:28 PM - Aaron Bertrand Back To Top

Yep, got it, this was a starter script that didn't consider cases where FKs may be explicitly defined for replication, have cascade options, or be disabled. The reader should be aware that this only deals with the most straightforward case. Perhaps I will write a follow-up tip that handles a lot more scenarios...


Friday, October 17, 2014 - 10:10:01 AM - John Q Martin Back To Top

Great script Aaron, one thing that has tripped me up in the past when doing this is that for some reason people will insist on disabling foreign keys in systems resulting in inconsistencies in the data that will cause the creates to fail. Just something to bear in mind when performing an operation such as this.


Friday, October 17, 2014 - 2:53:51 AM - alzdba Back To Top

Nice case, Aaron !

Great starting script with a nice use of the For XML path clause. (we often forget its power(s))

All it needs are the foreign key options ( check/nocheck, on delete action, on update action, replication actions ) and of course, doing it this way one will lose the is_system_named metadata value for the FK name ( if that matters at all ). These options can be pulled from the (used) sys.foreign_keys object.

On top of that, IMO anyone usign this kind of script should pay attention to the is_not_thrusted property, as it has a huge impact on how the SQLServer engine will treat the relationship with regards to SQLPlan impact!

Cheers,

Johan


Monday, October 13, 2014 - 1:51:32 PM - Carl Back To Top

Like others I have been meaning to do this for ages, in order to change the collation across a database.

I need to drop quite a few Check_Contstraints as well, so here are the additions. If you've worked out how it works it should be pretty obvious where to put them!

 

Drops:

SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
    + ' DROP CONSTRAINT ' + QUOTENAME(ck.name) + ';'
FROM sys.check_constraints AS ck
INNER JOIN sys.tables AS ct
  ON ck.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
  ON ct.[schema_id] = cs.[schema_id];

 

Creates:

SELECT @create += N'
ALTER TABLE '
+ QUOTENAME(cs.name) + '.'
+ QUOTENAME(ct.name) + '  WITH CHECK ADD  CONSTRAINT ' + QUOTENAME(ck.name)
    + ' CHECK  ' + ck.definition + ';'
FROM sys.check_constraints AS ck
INNER JOIN sys.tables AS ct
  ON ck.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
  ON ct.[schema_id] = cs.[schema_id];

select N'
ALTER TABLE '
+ QUOTENAME(cs.name) + '.'
+ QUOTENAME(ct.name) + ' CHECK CONSTRAINT ' + QUOTENAME(ck.name)
FROM sys.check_constraints AS ck
INNER JOIN sys.tables AS ct
  ON ck.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
  ON ct.[schema_id] = cs.[schema_id];

 

 

 


Tuesday, October 07, 2014 - 8:40:17 PM - Mike Back To Top

Aaron - I really appreciate you putting this together and sharing it with the community, your timing on this couldn't have been better.  I was about to sit down and try to create something similar either tonight or tomorrow.  Now I don't have to.  Keep up the good work.  Thanks again.


Tuesday, October 07, 2014 - 10:29:37 AM - Aaron Bertrand Back To Top

Thanks James, 

Yes, I certainly wasn't exhaustive there, thanks for bringing it up. Hopefully these will be trivial additions for the reader...

Aaron


Tuesday, October 07, 2014 - 8:01:10 AM - James Lean Back To Top

Nice script Aaron, I remember having to generate these scripts in SQL 2000, where FOR XML PATH wasn't an option!

 

One thing you might want to add is to retain any ON CASCADE options that may have been set, plus include the NOT FOR REPLICATION option where applicable.  These should be fairly easy to tack on the end of the create script, based on the columns in sys.foreign_keys.

 

Cheers,

James


Learn more about SQL Server tools