T-SQL to drop all Foreign Keys that reference a table

In a recent data migration project, I had a task to re-number some primary key columns for certain tables, so that there was no key overlap when a number of systems were combined. Part of this process involved identifying and dropping all the foreign keys that referenced the primary key on the tables that were being changed, then adding them again after the updates were completed. To save a bit of time, I knocked up this little script to generate the DROP and ALTER statements for the referencing foreign keys for a given table.

SELECT
    'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(fk.parent_object_id)
		+ ' DROP CONSTRAINT ' + fk.NAME + ' ;' AS DropStatement,
    'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(fk.parent_object_id)
	+ ' ADD CONSTRAINT ' + fk.NAME + ' FOREIGN KEY (' + COL_NAME(fk.parent_object_id, fkc.parent_column_id) 
		+ ') REFERENCES ' + ss.name + '.' + OBJECT_NAME(fk.referenced_object_id) 
		+ '(' + COL_NAME(fk.referenced_object_id, fkc.referenced_column_id) + ');' AS CreateStatement
FROM
	sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.schemas s ON fk.schema_id = s.schema_id
INNER JOIN sys.tables t ON fkc.referenced_object_id = t.object_id
INNER JOIN sys.schemas ss ON t.schema_id = ss.schema_id
WHERE
	OBJECT_NAME(fk.referenced_object_id) = 'account'
	AND ss.name = 'dbo';

5 comments

    • Ognyan Zhelezov

      Unfortunately this code does not generate correct script to re-create foreign keys if given table has as primary key two or more fields. Returned error in this case is “There are no primary or candidate keys in the referenced table …”

  1. pgnt

    while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE=’FOREIGN KEY’))
    begin
    declare @sql nvarchar(2000)
    SELECT TOP 1 @sql=(‘ALTER TABLE ‘ + TABLE_SCHEMA + ‘.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']‘)
    FROM information_schema.table_constraints
    WHERE CONSTRAINT_CATALOG = ‘myDB’ AND CONSTRAINT_TYPE = ‘FOREIGN KEY’
    exec (@sql)
    end

  2. Kyle

    Just came upon this, worked great for me. Got an error message or two but that may be how I am using it. Saved me a lot of time. Thanks!

Leave a Reply