SQL Server TSQL to find complete CONSTRAINT information on a table catalog?
I have had a requirement to see a detailed information about a constraint on a table that includes table fields, type, rules, referenced table and fields for FOREIGN KEYs, etc. Found this useful TSQL to get such information:
SELECT k.table_name, k.column_name field_name, c.constraint_type, CASE c.is_deferrable WHEN 'NO' THEN 0 ELSE 1 END 'is_deferrable', CASE c.initially_deferred WHEN 'NO' THEN 0 ELSE 1 END 'is_deferred', rc.match_option 'match_type', rc.update_rule 'on_update', rc.delete_rule 'on_delete', ccu.table_name 'references_table', ccu.column_name 'references_field', k.ordinal_position 'field_position' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c ON k.table_name = c.table_name AND k.table_schema = c.table_schema AND k.table_catalog = c.table_catalog AND k.constraint_catalog = c.constraint_catalog AND k.constraint_name = c.constraint_name LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON rc.constraint_schema = c.constraint_schema AND rc.constraint_catalog = c.constraint_catalog AND rc.constraint_name = c.constraint_name LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON rc.unique_constraint_schema = ccu.constraint_schema AND rc.unique_constraint_catalog = ccu.constraint_catalog AND rc.unique_constraint_name = ccu.constraint_name WHERE k.constraint_catalog = DB_NAME() AND k.table_name = 'testconstraints2' AND k.constraint_name = 'testconstraints_id_fk' ORDER BY k.constraint_name, k.ordinal_position
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.