REFERENTIAL_CONSTRAINTS

Information Schema Views Code behind (INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS)!!!


SELECT  DB_NAME() AS CONSTRAINT_CATALOG ,
        SCHEMA_NAME(f.schema_id) AS CONSTRAINT_SCHEMA ,
        f.name AS CONSTRAINT_NAME ,
        DB_NAME() AS UNIQUE_CONSTRAINT_CATALOG ,
        SCHEMA_NAME(t.schema_id) AS UNIQUE_CONSTRAINT_SCHEMA ,
        i.name AS UNIQUE_CONSTRAINT_NAME ,
        CONVERT(VARCHAR(7), 'SIMPLE') AS MATCH_OPTION ,
        CONVERT(VARCHAR(11), CASE f.update_referential_action
                               WHEN 0 THEN 'NO ACTION'
                               WHEN 1 THEN 'CASCADE'
                               WHEN 2 THEN 'SET NULL'
                               WHEN 3 THEN 'SET DEFAULT'
                             END) AS UPDATE_RULE ,
        CONVERT(VARCHAR(11), CASE f.delete_referential_action
                               WHEN 0 THEN 'NO ACTION'
                               WHEN 1 THEN 'CASCADE'
                               WHEN 2 THEN 'SET NULL'
                               WHEN 3 THEN 'SET DEFAULT'
                             END) AS DELETE_RULE
FROM    sys.foreign_keys f
        LEFT JOIN sys.indexes i ON i.object_id = f.referenced_object_id
                                   AND i.index_id = f.key_index_id
        LEFT JOIN sys.tables t ON t.object_id = f.referenced_object_id


Advertisements
This entry was posted in Database Administration, Information Schema. Bookmark the permalink.

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s