Summary: Use these T-SQL scripts to automate the dropping and recreating of foreign key constraints. This is essential for resetting database contents, performing bulk data loads, or reseeding identities.
Automating Foreign Key Management for Database Resets
Maintaining ready-to-use scripts for resetting database contents is a lifesaver for dev and QA environments. When you need to truncate tables or reseed identities, foreign key constraints often get in the way.
The following workflow captures your existing FK metadata into a temporary table, generates "Drop" scripts, and prepares "Recreate" scripts so you can restore your constraints instantly after your data cleanup.
1. Prepare Metadata Storage
We start by ensuring our temporary workspace is clean and then capture the complex relationships, including multi-column keys.
-- Drop temp table if it exists
IF OBJECT_ID('tempdb..#Fkey') IS NOT NULL
DROP TABLE #Fkey;
-- Capture Foreign Key Information
;WITH cte AS (
SELECT
fkc.constraint_column_id AS consColumn,
fk.NAME AS foreignKeyName,
parentSchema.name AS parentSchema,
parentTable.NAME AS parentTableName,
parent_col.NAME AS parentColName,
refSchema.name AS refSchema,
refTable.NAME AS refTableName,
ref_col.NAME AS refColName
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.tables parentTable ON parentTable.object_id = fkc.parent_object_id
INNER JOIN sys.schemas parentSchema ON parentSchema.schema_id = parentTable.schema_id
INNER JOIN sys.columns parent_col ON fkc.parent_column_id = parent_col.column_id
AND parent_col.object_id = parentTable.object_id
INNER JOIN sys.tables refTable ON refTable.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas refSchema ON refSchema.schema_id = refTable.schema_id
INNER JOIN sys.columns ref_col ON fkc.referenced_column_id = ref_col.column_id
AND ref_col.object_id = refTable.object_id
WHERE parentTable.type = 'U' AND refTable.type = 'U'
)
SELECT DISTINCT
foreignKeyName,
parentSchema,
parentTableName,
SUBSTRING((
SELECT ',' + a.parentColName
FROM cte a
WHERE a.foreignKeyName = c.foreignKeyName
ORDER BY a.consColumn
FOR XML PATH('')
), 2, 300000) AS parentColName,
refSchema,
refTableName,
SUBSTRING((
SELECT ',' + b.refColName
FROM cte b
WHERE b.foreignKeyName = c.foreignKeyName
ORDER BY b.consColumn
FOR XML PATH('')
), 2, 300000) AS refColName
INTO #Fkey
FROM cte c;
2. Generate "DROP" Scripts
Run the query below and copy the results to safely remove constraints.
SELECT DISTINCT
'IF EXISTS (SELECT * FROM sys.foreign_keys
WHERE object_id = OBJECT_ID(N''[' + parentSchema + '].[' + foreignKeyName + ']'')
AND parent_object_id = OBJECT_ID(N''[' + parentSchema + '].[' + ParentTableName + ']''))
ALTER TABLE [' + parentSchema + '].[' + ParentTableName + '] DROP CONSTRAINT [' + foreignKeyName + ']'
AS foreignKey_drop_script
FROM #Fkey;
3. Generate "RECREATE" Scripts
After your data operations are complete, run these generated scripts to restore referential integrity.
SELECT DISTINCT
'ALTER TABLE [' + parentSchema + '].[' + ParentTableName + '] WITH CHECK
ADD CONSTRAINT [' + foreignKeyName + '] FOREIGN KEY(' + parentColName + ')
REFERENCES [' + refSchema + '].[' + refTableName + '](' + refColName + ')'
AS Add_constraints_script
FROM #Fkey;
Additional Resources:
- How to Find SQL Server Object Dependency
- Identifying Missing Indexes in SQL Server
- Optimizing Foreign Key Performance
Working with Truncate? Remember that TRUNCATE TABLE is not allowed if a table is referenced by a Foreign Key, even if the table is empty. Use these scripts to drop them first!
