How to list constraints in an sql server database?
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
————————————————————————————————
How to list all constraints by table?
Execute the following Microsoft SQL Server T-SQL script to list all constraint in the database by schema, by table, by column:
use AdventureWorks;
select
c.TABLE_SCHEMA,
c.TABLE_NAME,
c.COLUMN_NAME,
c.CONSTRAINT_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as c
union all
select
s.name,
o.name,
c.name,
dc.name
from sys.default_constraints as dc
join sys.objects as o
on o.object_id = dc.parent_object_id
join sys.columns as c
on c.object_id = o.object_id
and c.column_id = dc.parent_column_id
join sys.schemas as s
on s.schema_id = o.schema_id
order by TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
SQL Server TSQL to find detailed CONSTRAINTS information
TO enforce the integrity of the data in the columns, CONSTRAINTS are very useful.
In this regard SQL Server provides various mechanims in enforcing such integrity:
- PRIMARY KEY Constraints
- FOREIGN KEY Constraints
- UNIQUE Constraints
- CHECK Constraints
- DEFAULT Definitions
- Allowing Null Values
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: 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.
Here is the script which can used to obtain a detailed information on CONSTRAINTS in your database:
SELECT KCU.table_name,
KCU.column_name field_name,
TC.constraint_type,
CASE TC.is_deferrable WHEN ‘NO’ THEN 0 ELSE 1 END ‘is_deferrable’,
CASE TC.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’,
KCU.ordinal_position ‘field_position’
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON KCU.table_name =TC.table_name
AND KCU.table_schema =TC.table_schema
AND KCU.table_catalog =TC.table_catalog
AND KCU.constraint_catalog =TC.constraint_catalog
AND KCU.constraint_name =TC.constraint_name
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
ON rc.constraint_schema =TC.constraint_schema
AND rc.constraint_catalog =TC.constraint_catalog
AND rc.constraint_name =TC.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 KCU.constraint_catalog = DB_NAME()
ORDER BY KCU.constraint_name,
KCU.ordinal_position;
