TSQLScripts.com: Script Details

TSQLScripts.com.

skip to the main content area of this page

Transact SQL scripts written by
DBAs and Developers for
DBAs and Developers.

To submit a comment about this script click here here or to submit your own script click here.

Drop and Recreate Foreign Keys
Description:
Identifying all the foreign key relationships that exist on a table can be easily accomplished using sys.foreign_keys. However, dropping the foreign keys and adding them back can be difficult if a script does not exist. This result of this script is a list of ALTER TABLE scripts that Drops all existing Foreign Keys on a table and Re-Creates those keys that were dropped. Replace the TABLENAME and SCHEMANAME with your values.
Submitted By: 2008DBA

set nocount on
declare @table sysname
declare @schema sysname

select
	@table = 'TABLENAME',
	@schema = 'SCHEMANAME'

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