SQL Server – List all Constraints of Database or Table

List all Constraints of Database or Table

Many times when we are using DML commands like: INSERT, UPDATE, DELETE, we get errors because one or the other constraint is set on the table. It get really annoying when we are new to a Database and don’t know what constraints are set on the tables.

It would be of great help if we get the list of all the constraints in the database. Below are two methods to do the same.

Method 1

Use sys.objects to get the constraint information.

— To Display all the Constraint in the Database

SELECT * FROM sys.objects

WHERE type_desc LIKE ‘%CONSTRAINT’

The above query will display all the fields of sys.objects. We can refine the above query to display the result more tasteful.

— To Display all the Constraints in the Database

SELECT OBJECT_NAME(object_id) AS ConstraintName,

SCHEMA_NAME(schema_id) AS SchemaName,

OBJECT_NAME(parent_object_id) AS TableName,

type_desc AS ConstraintType

FROM sys.objects

WHERE type_desc LIKE ‘%CONSTRAINT’

— To Display all the Constraints in table ‘Employee’

SELECT OBJECT_NAME(object_id) AS ConstraintName,

SCHEMA_NAME(schema_id) AS SchemaName,

type_desc AS ConstraintType

FROM sys.objects

WHERE type_desc LIKE ‘%CONSTRAINT’ AND OBJECT_NAME(parent_object_id)=‘Employee’

Method 2

Use INFORMATION_SCHEMA.TABLE_CONSTRAINTS to get the constraint information. This query has one shortcoming; it won’t display DEFAULT constraints of the database

— To Display all the Constraints in the Database

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

— To Display all the Constraints in table ‘Employee’

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE TABLE_NAME=‘Employee’

To display DEFAULT constraints in the database we can use following SQL Statement.

— To Display Default Constraints in Database

SELECT OBJECT_NAME(PARENT_OBJECT_ID) AS TABLE_NAME,

COL_NAME (PARENT_OBJECT_ID, PARENT_COLUMN_ID) AS COLUMN_NAME,

NAME AS DEFAULT_CONSTRAINT_NAME

FROM SYS.DEFAULT_CONSTRAINTS

See also:

SQL Server – Drop All Table Constraints

SQL Server – Stored Procedure to Drop All Database Constraints

Good Luck!