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:
