SELECT 'To' 'Direction',
OBJECT_NAME(sfk.constraint_object_id) 'ConstraintName',
OBJECT_NAME(parent_object_id) 'ConstrainedTable',
psc.name 'ConstrainedColumn',
OBJECT_NAME(referenced_object_id) 'ConstrainingTable',
csc.name 'ConstraingColumn',
'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + ' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(OBJECT_NAME(sfk.constraint_object_id)) + ' FOREIGN KEY(['+psc.name + ']) REFERENCES ' + OBJECT_NAME(referenced_object_id) +' ([' + csc.name +'])' AS [CreateScript] ,
'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(OBJECT_NAME(sfk.constraint_object_id)) AS [DropScript]
FROM sys.foreign_key_columns sfk
INNER JOIN sys.columns psc ON psc.column_id = sfk.parent_column_id
AND sfk.parent_object_id = psc.object_id
INNER JOIN sys.columns csc ON csc.column_id = sfk.referenced_column_id
AND sfk.referenced_object_id = csc.object_id
WHERE OBJECT_NAME(sfk.constraint_object_id) IN
(
SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
)
No comments:
Post a Comment