By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE A(id INT PRIMARY KEY)
CREATE TABLE B(id INT PRIMARY KEY, A_id INT CONSTRAINT fk_B_A FOREIGN KEY REFERENCES A(id)
ON DELETE CASCADE
)
CREATE TABLE C(id INT PRIMARY KEY, A_id INT CONSTRAINT fk_C_A FOREIGN KEY REFERENCES A(id)
ON DELETE CASCADE
)
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]
WHERE delete_referential_action_desc <> 'NO_ACTION';
-- 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
ALTER TABLE [dbo].[B] DROP CONSTRAINT [fk_B_A];
ALTER TABLE [dbo].[C] DROP CONSTRAINT [fk_C_A];
ALTER TABLE [dbo].[B] ADD CONSTRAINT [fk_B_A] FOREIGN KEY ([A_id]) REFERENCES [dbo].[A]([id]);
ALTER TABLE [dbo].[C] ADD CONSTRAINT [fk_C_A] FOREIGN KEY ([A_id]) REFERENCES [dbo].[A]([id]);