Drop all foreign keys which reference a table

Problem: Cant drop a table because other tables reference it with their foriegn keys

Solution:

SELECT fk.name,’ALTER TABLE ‘ + s.name + ‘.’ + o_parent.name + ‘ DROP CONSTRAINT ‘ + fk.name ‘tsql’
FROM sys.foreign_keys fk
INNER JOIN sys.objects o on o.object_id = fk.referenced_object_id
INNER JOIN sys.objects o_parent on o_parent.object_id = fk.parent_object_id
INNER JOIN sys.schemas s on s.schema_id = o.schema_id
WHERE s.name = @schema and o.name=@table and o.object_id != o_parent.object_id

Copy the second column into a new window and execute

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: