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
- Tuck this script away in your toolkit, and test it out on your most complex SQL Server databases.
- Review the following tips and other resources:
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He also blogs at sqlblog.org.
- MSSQLTips Awards: Author of the Year – 2016, 2023 | Leadership (200+ tips) – 2022
@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;
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/
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?
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).
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).
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?
Thank you so much Aaron Bertrand and you did save lot of time, thanks again !
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.
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.
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.