FDiri
12/1/2015 - 8:55 PM

create scripts which finds foreign key constraints and rebuild them by deleting and creating foreign constraints.

create scripts which finds foreign key constraints and rebuild them by deleting and creating foreign constraints.

DECLARE @tableName NVARCHAR(500)
SET @tableName='_Neighborhood'
SELECT
    fk.name,
    object_name(fk.parent_object_id) 'Parent table',
    c1.name 'Parent column',
    object_name(fk.referenced_object_id) 'Referenced table',
    c2.name 'Referenced column',
	'ALTER TABLE dbo.' + object_name(fk.parent_object_id) + 
    ' DROP CONSTRAINT [' + fk.name+']',
	'ALTER TABLE dbo.' + object_name(fk.parent_object_id) + 
    ' ADD CONSTRAINT [' + fk.name +
    '] FOREIGN KEY(' + c1.name + ') REFERENCES dbo.' + 
    object_name(fk.referenced_object_id) + '(' + c2.name + ')'
from 
    sys.foreign_keys fk
inner join
    sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
inner join
    sys.columns c1 ON fkc.parent_column_id = c1.column_id and c1.object_id = fkc.parent_object_id
inner join
    sys.columns c2 ON fkc.referenced_column_id = c2.column_id and c2.object_id = fkc.referenced_object_id
	WHERE object_name(fk.referenced_object_id)=@tableName