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

How to fix blurry images in Google Chrome

Problem When browsing sites with thumbnail images, those images appear blurred in Chrome, but not in other browsers such as Internet Explore...