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!
Posted by Richa Aggarwal Gupta on August 12, 2011 at 8:21 pm
you can also use sp_helpconstraint N’tableName’ to see all the constraints set on a table.