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

By:   |   Updated: 2014-10-06   |   Comments (46)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Constraints


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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2014-10-06

Comments For This Article




Friday, November 12, 2021 - 12:36:19 PM - Christodoulos M. Back To Top (89442)
@Aaron I made the following adjustments to your initial create FK script part:

SELECT N'
ALTER TABLE '
+ QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ case when fk.is_not_trusted = 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'')
+ ') ON DELETE '
+ 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
+ ' ON UPDATE '
+ 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_for_replication when 1 then ' NOT FOR REPLICATION;' ELSE ';' END
-- disable FK if it was disabled
+ case when fk.is_disabled = 1 then
+ char(10) + 'ALTER TABLE '
+ QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' NOCHECK CONSTRAINT ' + QUOTENAME(fk.name) + ';'
ELSE ''
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;

Wednesday, September 29, 2021 - 4:02:35 PM - Aaron Bertrand Back To Top (89291)
Aglar, is it possible you are relying on print output and the commands are being truncated because of SSMS output limits?

See this:

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

Wednesday, September 29, 2021 - 3:26:26 PM - �ağlar can sarıkaya Back To Top (89290)
Aaron that really great query. I missed something it creates a delete query for deleting 24 FK, but the create query is just for 16 FK. Where is other 8 in my case?

Monday, September 27, 2021 - 8:30:54 AM - Aaron Bertrand Back To Top (89283)
Ibrahim, sorry for the delay, yes if the script is long you'll be dealing with print limitations of SSMS. See this for a workarond:

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

As for just handling a single table, you would need to add a where clause on ct.name = @tablename and rt.name = @tablename. I would also validate the schema in both places (but I do acknowledge that a lot of shops use only dbo).

Monday, September 27, 2021 - 8:27:29 AM - Aaron Bertrand Back To Top (89282)
Hey Adam,

1. You could delete instead of truncate (or delete in batches if the table is large). It's just fully logged.

2. You could determine the views that reference the table *and* are schema-bound using OBJECTPROPERTY/IsSchemaBound, and re-create them temporarily without schemabinding while you make changes to the underlying table, but this gets complicated if the view is schema-bound for the purpose of creating an indexed view, because you'll also have to deal with the logic of re-creating that index (and all the additional logging that will create).


Saturday, September 25, 2021 - 9:21:07 PM - Adam Tsiopani Back To Top (89266)
Thanks Aaron, this is extremely useful. I'm using it in my project and it's saved a ton of time (and transaction logs) bypassing the DELETE statement.

I have one use case where it has not worked. If you have a view WITH SCHEMABINDING referencing the table, it will bomb out with: ` Cannot TRUNCATE TABLE 'MySchema.MyTable' because it is being referenced by object 'vSomeView'. Do you know of any way to get around this?

Wednesday, September 1, 2021 - 9:11:17 AM - satish Back To Top (89199)
Thank you so much Aaron Bertrand and you did save lot of time, thanks again !

Wednesday, June 16, 2021 - 2:35:28 AM - ibrahim Back To Top (88863)
my create_script is coming out truncated both in print and in table column., can you tell me what could be the reason, also how can we make this script work for a specific table only? thank you.

Wednesday, April 14, 2021 - 12:41:08 PM - Aaron Bertrand Back To Top (88538)
Bob, those are there for your protection. You don’t have to be using a foreign language to be bitten by wrong data or worse due to Unicode characters. I make it a point to always use N prefixes on string literals because I don’t know what every reader is dealing with in their own systems, or what I’ll be dealing with when I come to grab the code later.

Wednesday, April 14, 2021 - 11:51:13 AM - Bob Letts Back To Top (88537)
Great script. I don't use Katana, Cantonese, or Japanese double-byte values, so I changed all the Unicode variables to VARCHAR and took all the N' type casting out.

Thursday, June 11, 2020 - 11:17:23 PM - Peter Back To Top (85974)

Great script.  I only wanted to go it for one referenced table so added a "fk.referenced_object_id = object_id(N'???')".  Also suffered the issue with the print statement truncation but doing a select on @drop, @create sorted those.


Thursday, April 9, 2020 - 11:48:04 AM - Aaron Bertrand Back To Top (85315)

Mia, are you printing the command in SSMS and then copying/pasting to run it? See: https://www.mssqltips.com/sqlservertip/3185/validate-the-contents-of-large-dynamic-sql-strings-in-sql-server/


Thursday, April 9, 2020 - 10:44:23 AM - Mia D Johnson Back To Top (85310)

I'm running into an issue where the datatype for the create FK statements is not big enough.  My final string is being cut off by part of the last two create statements so it's erroring when it runs.  Is there a way to convert this functionality so that it uses two variables for both drop and create statements, i.e. drop 1 and drop 2 and create1 and create2 and it prints them out together at the end?

Mia J


Friday, January 31, 2020 - 12:25:13 PM - Mateus Taroda Back To Top (84069)

Thank you Aaron and Alex, for the script and the evolution of the script.

This was really helpfull!


Thursday, October 25, 2018 - 9:03:22 AM - Aaron Bertrand Back To Top (78054)

Alex, the varchar(max) *can* absolutely hold the whole script. Management Studio just doesn't have a direct way to prove it to you, since it truncates output whether you use print or select, results to text or results to grid, etc. See https://www.mssqltips.com/sqlservertip/3185/validate-the-contents-of-large-dynamic-sql-strings-in-sql-server/ 

 


Thursday, October 25, 2018 - 3:36:55 AM - alex Back To Top (78052)

 If the database has many fkeys then the varchar(max) can not hold the whole script. the following version creates multiple lines into the table, one for each drop, create.

Thanks Aaron for the great script!

CREATE TABLE AM_FKEYZ -- feel free to use a permanent table
(
  [type] VARCHAR(10),
  create_script VARCHAR(MAX)
);

-- drop is easy, just build a simple concatenated list from sys.foreign_keys:
insert into AM_FKEYZ
SELECT
'drop', '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];

-- 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.
insert into AM_FKEYZ
SELECT 'create',
'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;

select * from AM_FKEYZ;

 


Thursday, August 23, 2018 - 2:46:12 PM - Aaron Bertrand Back To Top (77295)

Jun, does EF enforce relationships in some way other than with explicit foreign key constraints? EF should really have nothing to do with it. Can you show a minimal repro where FK constraints are defined within SQL Server and the above script misses them?


Thursday, August 23, 2018 - 12:56:53 PM - Jun Back To Top (77293)

 The idea is sound, but the script above misses about half of the FKs on EF-generated databases with custom schemas. I'll figure some other way. Let me know if you are interested in improving the script above - I can show you cases where it doesn't find the FK that's already in.


Saturday, June 9, 2018 - 9:14:37 AM - Hove Back To Top (76172)

I use it in a way where I generate the create script for all tables through SSMS, SSMS first generates create tables and after, all constraints, everything grouped and I write the simple script to delete all foreign key constraints. Very little hustle and no errors.


Friday, April 20, 2018 - 6:15:37 PM - Ibrahim Back To Top (75744)

 

 Thanks for doing the Grunt Work!!  It's an excellant solution.


Wednesday, April 4, 2018 - 3:34:47 AM - Michel Manias Back To Top (75598)

Thanks Aaron this TSQL is very good,
If I may, I would have added the IF EXISTS clause just after the DROP CONSTRAINT like that we can run it many times.


Thursday, December 14, 2017 - 3:44:40 AM - Juozas Back To Top (73995)

 Hi, 

Foreign key script: its better to use char(10) + char(13)  instead of char(13).

 


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

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 (57757)

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 (56329)

 Great script thanks!!


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

 Loved this script! Thank you.

 


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

This saved me a ton of time. Thank you.


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

 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 (43398)

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 (41478)

 

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 (38499)

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 (36674)

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 (36506)

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

 


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

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 (35934)

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 (35933)

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 (35931)

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 3, 2015 - 6:06:40 AM - alzdba Back To Top (35814)

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 4, 2014 - 1:43:48 AM - Sumit Rastogi Back To Top (35176)

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 (34997)

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 (34992)

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 (34988)

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 (34941)

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 7, 2014 - 8:40:17 PM - Mike Back To Top (34874)

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 7, 2014 - 10:29:37 AM - Aaron Bertrand Back To Top (34864)

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 7, 2014 - 8:01:10 AM - James Lean Back To Top (34862)

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















get free sql tips
agree to terms