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',...(
read more)
Read the complete post at http://sqlserver-qa.net/blogs/t-sql/archive/2008/01/09/2475.aspx