How to Drop and Recreate Foreign Key Constraints
The following script provides for a convenient to generate the drop foreign key commands and (more importantly) their corresponding “recreate foreign key” commands which allow you to easily manipulate data across constrained tables.
Care should be using this script on high transaction volume systems where there may be a risk of un-constrained data entry taking place and potentially impacting on the data integrity of the environment – not to mention complications when trying to recreate the FK constraints again.
Just update the @table and @schema variables below, and you should be good to go!
set nocount on declare @table sysname declare @schema sysname select @table = 'TABLE', @schema = 'SCHEMA' print '/*Drop Foreign Key Statements for ['+@schema+'].['+@table+']*/' select 'ALTER TABLE ['+SCHEMA_NAME(o.schema_id)+'].['+ o.name+'] DROP CONSTRAINT ['+fk.name+']' from sys.foreign_keys fk inner join sys.objects o on fk.parent_object_id = o.object_id where o.name = @table and SCHEMA_NAME(o.schema_id) = @schema print '/*Create Foreign Key Statements for ['+@schema+'].['+@table+']*/' select 'ALTER TABLE ['+SCHEMA_NAME(o.schema_id)+'].['+o.name+'] ADD CONSTRAINT ['+fk.name+'] FOREIGN KEY (['+c.name+']) REFERENCES ['+SCHEMA_NAME(refob.schema_id)+'].['+refob.name+'](['+refcol.name+'])' from sys.foreign_key_columns fkc inner join sys.foreign_keys fk on fkc.constraint_object_id = fk.object_id inner join sys.objects o on fk.parent_object_id = o.object_id inner join sys.columns c on fkc.parent_column_id = c.column_id and o.object_id = c.object_id inner join sys.objects refob on fkc.referenced_object_id = refob.object_id inner join sys.columns refcol on fkc.referenced_column_id = refcol.column_id and fkc.referenced_object_id = refcol.object_id where o.name = @table and SCHEMA_NAME(o.schema_id) = @schema