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;

 

extradrmtech

Since 20 years I work on Database Architecture and data migration protocols. I am also a consultant in Web content management solutions. I am an experienced web-developer with over 10 years developing PHP/MySQL, C#, VB.Net applications ranging from simple web sites to extensive web-based business applications. When not writing code, I like to dance salsa and swing and have fun with my little family.

You may also like...