Feb 3, 2012

Drop and Recreate Foreign Keys


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
       

No comments:

Post a Comment

Hi,

Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.