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 !!!