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 in database
------------
-- MSSQL information_schema views
USE AdventureWorks;
SELECT TABLE_NAME,
COLUMN_NAME,
CHECK_CLAUSE,
cc.CONSTRAINT_SCHEMA,
cc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
ON cc.CONSTRAINT_NAME = c.CONSTRAINT_NAME
ORDER BY CONSTRAINT_SCHEMA,
TABLE_NAME,
COLUMN_NAME
GO
/* Partial results
| TABLE_NAME |
COLUMN_NAME |
CHECK_CLAUSE |
| SalesOrderHeader |
OrderDate |
([DueDate]>=[OrderDate]) |
| SalesOrderHeader |
OrderDate |
([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL) |
| SalesOrderHeader |
ShipDate |
([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL) |
| SalesOrderHeader |
Status |
([Status]>=(0) AND [Status]<=(8)) |
| SalesOrderHeader |
SubTotal |
([SubTotal]>=(0.00)) |
| SalesOrderHeader |
TaxAmt |
([TaxAmt]>=(0.00)) |
| SalesPerson |
Bonus |
([Bonus]>=(0.00)) |
| SalesPerson |
CommissionPct |
([CommissionPct]>=(0.00)) |
| SalesPerson |
SalesLastYear |
([SalesLastYear]>=(0.00)) |
| SalesPerson |
SalesQuota |
([SalesQuota]>(0.00)) |
| SalesPerson |
SalesYTD |
([SalesYTD]>=(0.00)) |
*/
-- SQL CHECK constraints: table name wildcard search
SELECT TABLE_NAME,
COLUMN_NAME,
CHECK_CLAUSE,
cc.CONSTRAINT_SCHEMA,
cc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
ON cc.CONSTRAINT_NAME = c.CONSTRAINT_NAME
WHERE TABLE_NAME LIKE '%CONTACT%'
ORDER BY CONSTRAINT_SCHEMA,
TABLE_NAME,
COLUMN_NAME
GO
------------ |
