It's helpful to maintain ready-to-use scripts for resetting database contents, including reseeding identities. Feel free to adjust these scripts to fit your specific needs.
Drop Temporary Table
IF OBJECT_ID('tempdb..#Fkey') IS NOT NULL
DROP TABLE #Fkey;
Create Temporary Table with 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;
Generate Scripts to Drop Foreign Keys
-- Generate scripts to drop existing foreign key 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;
Generate Scripts to Recreate Foreign Keys
-- Generate scripts to recreate the foreign key constraints
SELECT DISTINCT
'ALTER TABLE [' + parentSchema + '].[' + ParentTableName + '] WITH CHECK
ADD CONSTRAINT [' + foreignKeyName + '] FOREIGN KEY(' + parentColName + ')
REFERENCES [' + refSchema + '].[' + refTableName + '](' + refColName + ')'
AS Add_constraints_script
FROM #Fkey;
GO
Additional Resources:
