SQL Script to alter ALL Foreign Keys to add ON DELETE CASCADE
Here's a script I used for a similiar purpose. It does not support composite foreign keys (which use more than one field.) And it would probably need some tweaking before it will work for your situation. EDIT: In particular it does not handle multi-column foreign keys correctly.
select
DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema +
'].[' + ForeignKeys.ForeignTableName +
'] DROP CONSTRAINT [' + ForeignKeys.ForeignKeyName + ']; '
, CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema +
'].[' + ForeignKeys.ForeignTableName +
'] WITH CHECK ADD CONSTRAINT [' + ForeignKeys.ForeignKeyName +
'] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn +
']) REFERENCES [' + schema_name(sys.objects.schema_id) + '].[' +
sys.objects.[name] + ']([' +
sys.columns.[name] + ']) ON DELETE CASCADE; '
from sys.objects
inner join sys.columns
on (sys.columns.[object_id] = sys.objects.[object_id])
inner join (
select sys.foreign_keys.[name] as ForeignKeyName
,schema_name(sys.objects.schema_id) as ForeignTableSchema
,sys.objects.[name] as ForeignTableName
,sys.columns.[name] as ForeignTableColumn
,sys.foreign_keys.referenced_object_id as referenced_object_id
,sys.foreign_key_columns.referenced_column_id as referenced_column_id
from sys.foreign_keys
inner join sys.foreign_key_columns
on (sys.foreign_key_columns.constraint_object_id
= sys.foreign_keys.[object_id])
inner join sys.objects
on (sys.objects.[object_id]
= sys.foreign_keys.parent_object_id)
inner join sys.columns
on (sys.columns.[object_id]
= sys.objects.[object_id])
and (sys.columns.column_id
= sys.foreign_key_columns.parent_column_id)
) ForeignKeys
on (ForeignKeys.referenced_object_id = sys.objects.[object_id])
and (ForeignKeys.referenced_column_id = sys.columns.column_id)
where (sys.objects.[type] = 'U')
and (sys.objects.[name] not in ('sysdiagrams'))
Andomar's answer above is good but works for single-column foreign key constraints only. I adapted it a little for multi-column constraints:
create function dbo.fk_columns (@constraint_object_id int)
returns varchar(255)
as begin
declare @r varchar(255)
select @r = coalesce(@r + ',', '') + c.name
from sys.foreign_key_columns fkc
join sys.columns c
on fkc.parent_object_id = c.object_id
and fkc.parent_column_id = c.column_id
where fkc.constraint_object_id = @constraint_object_id
return @r
end
select distinct
DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema +
'].[' + ForeignKeys.ForeignTableName +
'] DROP CONSTRAINT [' + ForeignKeys.ForeignKeyName + '] '
, CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema +
'].[' + ForeignKeys.ForeignTableName +
'] WITH CHECK ADD CONSTRAINT [' + ForeignKeys.ForeignKeyName +
'] FOREIGN KEY(' + dbo.fk_columns(constraint_object_id) + ')' +
'REFERENCES [' + schema_name(sys.objects.schema_id) + '].[' +
sys.objects.[name] + '] '
+ ' ON DELETE CASCADE'
from sys.objects
inner join sys.columns
on (sys.columns.[object_id] = sys.objects.[object_id])
inner join (
select sys.foreign_keys.[name] as ForeignKeyName
,schema_name(sys.objects.schema_id) as ForeignTableSchema
,sys.objects.[name] as ForeignTableName
,sys.columns.[name] as ForeignTableColumn
,sys.foreign_keys.referenced_object_id as referenced_object_id
,sys.foreign_key_columns.referenced_column_id as referenced_column_id
,sys.foreign_keys.object_id as constraint_object_id
from sys.foreign_keys
inner join sys.foreign_key_columns
on (sys.foreign_key_columns.constraint_object_id
= sys.foreign_keys.[object_id])
inner join sys.objects
on (sys.objects.[object_id]
= sys.foreign_keys.parent_object_id)
inner join sys.columns
on (sys.columns.[object_id]
= sys.objects.[object_id])
and (sys.columns.column_id
= sys.foreign_key_columns.parent_column_id)
-- Uncomment this if you want to include only FKs that already
-- have a cascade constraint.
-- where (delete_referential_action_desc = 'CASCADE' or update_referential_action_desc = 'CASCADE')
) ForeignKeys
on (ForeignKeys.referenced_object_id = sys.objects.[object_id])
and (ForeignKeys.referenced_column_id = sys.columns.column_id)
where (sys.objects.[type] = 'U')
and (sys.objects.[name] not in ('sysdiagrams'))
You can also use the query to help remove ON DELETE CASCADE
from FKs that currently have it.
This still doesn't handle the case where the columns are named different things in the two tables - another user-defined function would need to be defined for that.