This is a quick post to generate the DROP/CREATE statements for all foreign keys referencing a given primary key... like the subject says. 😉 In this example, we generate the statements for all foreign keys referencing PK_Address_AddressID on Person.Address in AdventureWorks2012, and this is the output:
See this post for a little more detail on when you might need to do this.
CREATE TABLE #ForeignKeys (
SchemaName VARCHAR(128),
TableName VARCHAR(128),
ForeignKeyName VARCHAR(128),
DropScript VARCHAR(MAX),
CreateScript VARCHAR(MAX)
);
DECLARE @PKname NVARCHAR(128) = 'PK_Address_AddressID' --<-- change this to your PK name
DECLARE @create NVARCHAR(MAX) = '';
-- populate the drop statement
INSERT #ForeignKeys (SchemaName,TableName,ForeignKeyName,DropScript,CreateScript)
SELECT schemas.[name],
tables.[name],
foreign_keys.[name],
'ALTER TABLE ' + QUOTENAME(schemas.[name]) + '.' + QUOTENAME(tables.[name]) + ' DROP CONSTRAINT
' + QUOTENAME(foreign_keys.[name]),
NULL
FROM
sys.foreign_keys INNER JOIN sys.tables
ON foreign_keys.parent_object_id = tables.object_id INNER JOIN sys.schemas
ON tables.schema_id = schemas.schema_id INNER JOIN sys.indexes
ON sys.indexes.object_id = sys.foreign_keys.referenced_object_id
AND sys.indexes.index_id = sys.foreign_keys.key_index_id
WHERE
indexes.name = @PKname;
-- populate the create statement
UPDATE #ForeignKeys
SET CreateScript = Creation.Script
FROM #ForeignKeys INNER JOIN
(
SELECT
cs.[name] SchemaName,
ct.[name] TableName,
foreign_keys.[name] ForeignKeyName,
'ALTER TABLE ' + QUOTENAME(cs.[name]) + '.' + QUOTENAME(ct.[name]) + ' ADD CONSTRAINT ' + QUOTENAME(foreign_keys.[name]) + ' FOREIGN KEY (' +
STUFF((
SELECT ',' + QUOTENAME(columns.[name])
FROM sys.columns INNER JOIN sys.foreign_key_columns
ON foreign_key_columns.parent_column_id = columns.column_id
AND foreign_key_columns.parent_object_id = columns.object_id
WHERE
foreign_key_columns.constraint_object_id = foreign_keys.object_id
ORDER BY
foreign_key_columns.constraint_column_id
FOR XML PATH(''), TYPE
).value('.1', 'VARCHAR(MAX)'), 1, 1,'') + ') REFERENCES ' + QUOTENAME(schemas.[name]) + '.' + QUOTENAME(tables.[name]) + '(' +
STUFF((
SELECT ',' + QUOTENAME(columns.[name])
FROM sys.columns INNER JOIN sys.foreign_key_columns fkc
ON fkc.referenced_column_id = columns.column_id
AND fkc.referenced_object_id = columns.object_id
WHERE
fkc.constraint_object_id = foreign_keys.object_id
ORDER BY
fkc.constraint_column_id
FOR XML PATH(''), TYPE
).value('.1', 'NVARCHAR(MAX)'), 1, 1, '') + ')' Script
FROM sys.foreign_keys INNER JOIN sys.tables
ON foreign_keys.referenced_object_id = tables.object_id INNER JOIN sys.schemas
ON tables.schema_id = schemas.schema_id INNER JOIN sys.tables ct
ON foreign_keys.parent_object_id = ct.object_id INNER JOIN sys.schemas cs
ON ct.schema_id = cs.schema_id
WHERE
tables.is_ms_shipped = 0
AND ct.is_ms_shipped = 0
) Creation
ON Creation.SchemaName = #ForeignKeys.SchemaName
AND Creation.TableName = #ForeignKeys.TableName
AND Creation.ForeignKeyName = #ForeignKeys.ForeignKeyName
-- output your datta
SELECT
SchemaName,
TableName,
ForeignKeyName,
DropScript,
CreateScript
FROM
#ForeignKeys
-- drop your tmp table
DROP TABLE #ForeignKeys
