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.

 

image

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’;

 

image

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

Happy Learning !!!

Advertisements

7 thoughts on “Get the list of all the constraints in a table in SQL Server

  1. Pingback: Tweets that mention Get the list of all the constraints in a table in SQL Server « SQLThreads -- Topsy.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s