Tuesday, 6 November 2012

SQL to drop and recreate foreign keys

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