Get the list of all the constraints in a table in SQL Server

Often we think of How to get a list of all the constraints in a table. The below post answers that question. We can make use of INFORMATION_SCHEMA views to get the details of the meta data in SQL Server.

• INFORMATION_SCHEMA view is contained in each database.
• Each INFORMATION_SCHEMA view contains meta data about each object stored in that particular database.
  • information schema views are defined in a special schema named INFORMATION_SCHEMA.

The below query will list all the details about the meta data available in that particular database.

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

For example the above query will give you the below output i.e. List of all the constraints in a particular database. You can even filter data by specific constraint type , schema name, table name etc.

 

Example 2: List all the constraint in a table Employee under Schema STTest.

Answer: Following query will result the correct answer

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   WHERE TABLE_NAME ='Employee' AND TABLE_SCHEMA ='STTest';  

Note: Here TABLE_CONSTRAINTS is a view defined in a special schema named INFORMATION_SCHEMA.

Happy Learning !!!