SQL Server – 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 or in a specific table. Below are two methods to do the same.

Method 1: using in-built Store Procedure (sp_helpconstraint) we can get the constraint information.
Benefit of this Store Procedure is, it list a column name of a table for every constraint.

Syntax:

sp_helpconstraint <Table_Name>;

Example:

sp_helpconstraint users

Output:

SQL Server – List all Constraints of Database or Table

Method 2: Using sys.objects table we can get the constraint information.

Syntax:

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
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' and OBJECT_NAME(parent_object_id)='&lt;Table_Name&gt;'

Example:

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
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' and OBJECT_NAME(parent_object_id)='users'

Output:

SQL Server – List all Constraints of Database or Table

If you wan t to list all Constraints of Database then use below query,

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
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'

 Output:

SQL Server – List all Constraints of Database or Table


<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.