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 !!!
Pingback: Tweets that mention Get the list of all the constraints in a table in SQL Server « SQLThreads -- Topsy.com
thanks!
LikeLike
hi
LikeLike
very good
LikeLike
good
LikeLiked by 1 person
its very helpful to me thanks sir,
LikeLike
Hello Srikanth, thanks for your quick note.. I am glad that it helped you.
LikeLike