{"id":376,"date":"2012-01-13T07:49:16","date_gmt":"2012-01-13T05:49:16","guid":{"rendered":"http:\/\/www.extradrm.com\/?p=376"},"modified":"2013-06-18T20:55:20","modified_gmt":"2013-06-18T18:55:20","slug":"how-to-list-all-check-constraints-in-the-database","status":"publish","type":"post","link":"http:\/\/www.extradrm.com\/?p=376","title":{"rendered":"How to list constraints in an sql server database?"},"content":{"rendered":"

Execute the following Microsoft T-SQL example scripts in SQL Server Management Studio Query Editor to list all or specific CHECK constraints in AdventureWorks database.<\/span>
\n————
\n— Microsoft SQL Server T-SQL list all check constraints in database
\n————
\n— MSSQL information_schema views
\nUSE AdventureWorks;
\nSELECT\u00a0\u00a0 TABLE_NAME,
\nCOLUMN_NAME,
\nCHECK_CLAUSE,
\ncc.CONSTRAINT_SCHEMA,
\ncc.CONSTRAINT_NAME
\nFROM\u00a0\u00a0\u00a0\u00a0 INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
\nINNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
\nON cc.CONSTRAINT_NAME = c.CONSTRAINT_NAME
\nORDER BY CONSTRAINT_SCHEMA,
\nTABLE_NAME,
\nCOLUMN_NAME
\nGO
\n\/* Partial results<\/p>\n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
TABLE_NAME<\/td>\nCOLUMN_NAME<\/td>\nCHECK_CLAUSE<\/td>\n<\/tr>\n
SalesOrderHeader<\/td>\nOrderDate<\/td>\n([DueDate]>=[OrderDate])<\/td>\n<\/tr>\n
SalesOrderHeader<\/td>\nOrderDate<\/td>\n([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)<\/td>\n<\/tr>\n
SalesOrderHeader<\/td>\nShipDate<\/td>\n([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)<\/td>\n<\/tr>\n
SalesOrderHeader<\/td>\nStatus<\/td>\n([Status]>=(0) AND [Status]<=(8))<\/td>\n<\/tr>\n
SalesOrderHeader<\/td>\nSubTotal<\/td>\n([SubTotal]>=(0.00))<\/td>\n<\/tr>\n
SalesOrderHeader<\/td>\nTaxAmt<\/td>\n([TaxAmt]>=(0.00))<\/td>\n<\/tr>\n
SalesPerson<\/td>\nBonus<\/td>\n([Bonus]>=(0.00))<\/td>\n<\/tr>\n
SalesPerson<\/td>\nCommissionPct<\/td>\n([CommissionPct]>=(0.00))<\/td>\n<\/tr>\n
SalesPerson<\/td>\nSalesLastYear<\/td>\n([SalesLastYear]>=(0.00))<\/td>\n<\/tr>\n
SalesPerson<\/td>\nSalesQuota<\/td>\n([SalesQuota]>(0.00))<\/td>\n<\/tr>\n
SalesPerson<\/td>\nSalesYTD<\/td>\n([SalesYTD]>=(0.00))<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

*\/<\/p>\n

— SQL CHECK constraints: table name wildcard search
\nSELECT\u00a0\u00a0 TABLE_NAME,
\nCOLUMN_NAME,
\nCHECK_CLAUSE,
\ncc.CONSTRAINT_SCHEMA,
\ncc.CONSTRAINT_NAME
\nFROM\u00a0\u00a0\u00a0\u00a0 INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
\nINNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
\nON cc.CONSTRAINT_NAME = c.CONSTRAINT_NAME
\nWHERE\u00a0\u00a0\u00a0 TABLE_NAME LIKE ‘%CONTACT%’
\nORDER BY CONSTRAINT_SCHEMA,
\nTABLE_NAME,
\nCOLUMN_NAME
\nGO<\/p>\n

————————————————————————————————<\/p>\n

How to list all constraints by table?<\/h2>\n

Execute the following Microsoft SQL Server T-SQL script to list all constraint in the database by schema, by table, by column:<\/p>\n

use AdventureWorks;<\/p>\n

select<\/p>\n

c.TABLE_SCHEMA,<\/p>\n

c.TABLE_NAME,<\/p>\n

c.COLUMN_NAME,<\/p>\n

c.CONSTRAINT_NAME<\/p>\n

from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as c<\/p>\n

union all<\/p>\n

select<\/p>\n

s.name,<\/p>\n

o.name,<\/p>\n

c.name,<\/p>\n

dc.name<\/p>\n

from sys.default_constraints as dc<\/p>\n

join sys.objects as o<\/p>\n

on o.object_id = dc.parent_object_id<\/p>\n

join sys.columns as c<\/p>\n

on c.object_id = o.object_id<\/p>\n

and c.column_id = dc.parent_column_id<\/p>\n

join sys.schemas as s<\/p>\n

on s.schema_id = o.schema_id<\/p>\n

order by TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME<\/p>\n

SQL Server TSQL to find detailed CONSTRAINTS information<\/h2>\n

TO enforce the integrity of the data in the columns, CONSTRAINTS are very useful. <\/span><\/p>\n

In this regard SQL Server provides various mechanims in enforcing such integrity:<\/span><\/p>\n

\n
    \n
  • PRIMARY KEY Constraints <\/span><\/li>\n
  • FOREIGN KEY Constraints <\/span><\/li>\n
  • UNIQUE Constraints <\/span><\/li>\n
  • CHECK Constraints <\/span><\/li>\n
  • DEFAULT Definitions<\/span><\/li>\n
  • Allowing Null Values <\/span><\/li>\n<\/ul>\n

    Further steps are required in the database design to identify valid values for a column and to decide how to enforce the integrity of the data in the column. Data integrity falls into the following categories:\u00a0 Entity integrity\/Domain integrity\/Referential integrity\/User-defined integrity. Among these Referential integrity is commonly used which preserves the defined relationships between tables when rows are entered or deleted. In SQL Server, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys, through FOREIGN KEY and CHECK constraints. Referential integrity makes sure that key values are consistent across tables.<\/span><\/p>\n

    Here is the script which can used to obtain a detailed information on CONSTRAINTS in your database:<\/span><\/p>\n

    SELECT<\/span><\/span><\/span> KCU.<\/span><\/span>table_name,<\/span><\/span><\/span><\/span><\/p>\n

    KCU.<\/span><\/span>column_name field_name,<\/span><\/span><\/span><\/span><\/p>\n

    TC.<\/span><\/span>constraint_type,<\/span><\/span><\/span><\/span><\/p>\n

    CASE<\/span><\/span> TC.<\/span><\/span>is_deferrable WHEN<\/span><\/span> ‘NO’<\/span><\/span> THEN<\/span><\/span> 0 ELSE<\/span><\/span> 1 END<\/span><\/span> ‘is_deferrable’<\/span><\/span>,<\/span><\/span><\/span><\/span><\/p>\n

    CASE<\/span><\/span> TC.<\/span><\/span>initially_deferred WHEN<\/span><\/span> ‘NO’<\/span><\/span> THEN<\/span><\/span> 0 ELSE<\/span><\/span> 1 END<\/span><\/span> ‘is_deferred’<\/span><\/span>,<\/span><\/span><\/span><\/span><\/p>\n

    rc.<\/span><\/span>match_option ‘match_type’<\/span><\/span>,<\/span><\/span><\/span><\/span><\/p>\n

    rc.<\/span><\/span>update_rule ‘on_update’<\/span><\/span>,<\/span><\/span><\/span><\/span><\/p>\n

    rc.<\/span><\/span>delete_rule ‘on_delete’<\/span><\/span>,<\/span><\/span><\/span><\/span><\/p>\n

    ccu.<\/span><\/span>table_name ‘references_table’<\/span><\/span>,<\/span><\/span><\/span><\/span><\/p>\n

    ccu.<\/span><\/span>column_name ‘references_field’<\/span><\/span>,<\/span><\/span><\/span><\/span><\/p>\n

    KCU.<\/span><\/span>ordinal_position ‘field_position’<\/span><\/span><\/span><\/span><\/p>\n

    FROM<\/span><\/span> INFORMATION_SCHEMA<\/span><\/span>.<\/span><\/span>KEY_COLUMN_USAGE<\/span><\/span> KCU<\/span><\/span><\/p>\n

    LEFT<\/span><\/span> JOIN<\/span><\/span> INFORMATION_SCHEMA<\/span><\/span>.<\/span><\/span>TABLE_CONSTRAINTS<\/span><\/span> TC<\/span><\/span><\/p>\n

    ON<\/span><\/span> KCU.<\/span><\/span>table_name =<\/span><\/span>TC.<\/span><\/span>table_name<\/span><\/span><\/p>\n

    AND<\/span><\/span> KCU.<\/span><\/span>table_schema =<\/span><\/span>TC.<\/span><\/span>table_schema<\/span><\/span><\/p>\n

    AND<\/span><\/span> KCU.<\/span><\/span>table_catalog =<\/span><\/span>TC.<\/span><\/span>table_catalog<\/span><\/span><\/p>\n

    AND<\/span><\/span> KCU.<\/span><\/span>constraint_catalog =<\/span><\/span>TC.<\/span><\/span>constraint_catalog<\/span><\/span><\/p>\n

    AND<\/span><\/span> KCU.<\/span><\/span>constraint_name =<\/span><\/span>TC.<\/span><\/span>constraint_name<\/span><\/span><\/p>\n

    LEFT<\/span><\/span><\/span> JOIN<\/span><\/span> INFORMATION_SCHEMA<\/span><\/span>.<\/span><\/span>REFERENTIAL_CONSTRAINTS<\/span><\/span> rc<\/span><\/p>\n

    ON<\/span><\/span> rc.<\/span><\/span>constraint_schema =<\/span><\/span>TC.<\/span><\/span>constraint_schema<\/span><\/span><\/p>\n

    AND<\/span><\/span> rc.<\/span><\/span>constraint_catalog =<\/span><\/span>TC.<\/span><\/span>constraint_catalog<\/span><\/span><\/p>\n

    AND<\/span><\/span> rc.<\/span><\/span>constraint_name =<\/span><\/span>TC.<\/span><\/span>constraint_name<\/span><\/span><\/p>\n

    LEFT<\/span><\/span><\/span> JOIN<\/span><\/span> INFORMATION_SCHEMA<\/span><\/span>.<\/span><\/span>CONSTRAINT_COLUMN_USAGE<\/span><\/span> ccu<\/span><\/p>\n

    ON<\/span><\/span> rc.<\/span><\/span>unique_constraint_schema =<\/span><\/span> ccu.<\/span><\/span>constraint_schema<\/span><\/span><\/p>\n

    AND<\/span><\/span> rc.<\/span><\/span>unique_constraint_catalog =<\/span><\/span> ccu.<\/span><\/span>constraint_catalog<\/span><\/span><\/p>\n

    AND<\/span><\/span> rc.<\/span><\/span>unique_constraint_name =<\/span><\/span> ccu.<\/span><\/span>constraint_name<\/span><\/span><\/p>\n

    WHERE<\/span><\/span> KCU.<\/span><\/span>constraint_catalog =<\/span><\/span> DB_NAME<\/span><\/span>()<\/span><\/span><\/span><\/span><\/p>\n

    ORDER<\/span><\/span> BY<\/span><\/span> KCU.<\/span><\/span>constraint_name,<\/span><\/span><\/span><\/span><\/p>\n

    KCU.<\/span><\/span>ordinal_position<\/span>;<\/span><\/span><\/span><\/p>\n<\/div>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    Execute the following Microsoft T-SQL example scripts in SQL Server Management Studio Query Editor to list all or specific CHECK constraints in AdventureWorks database. ———— — Microsoft SQL Server T-SQL list all check constraints...<\/p>\n","protected":false},"author":1,"featured_media":2845,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[28,25],"tags":[47,104],"youtube_video":null,"_links":{"self":[{"href":"http:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/posts\/376"}],"collection":[{"href":"http:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=376"}],"version-history":[{"count":0,"href":"http:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/posts\/376\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/media\/2845"}],"wp:attachment":[{"href":"http:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=376"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=376"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=376"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}