SQL Server – List all Constraints of Database or Table

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!

Advertisements

5 responses to this post.

  1. you can also use sp_helpconstraint N’tableName’ to see all the constraints set on a table.

    Reply

  2. […] trouble shoot this error, I decided first to list all constrains in this table, for that I found this helpful post. In my table, all constrains names starts with DF_SANotify so to get all these constrains […]

    Reply

  3. Posted by Kris on June 27, 2013 at 12:44 am

    Bhavesh,
    I have SQLEXPRESS2012

    I ran below stmt
    SELECT distinct type_desc FROM sys.objects
    and output is
    SYSTEM_TABLE
    VIEW
    SQL_STORED_PROCEDURE
    SERVICE_QUEUE
    USER_TABLE
    INTERNAL_TABLE

    I don’t see CONSTRAINTS
    Could you please clarify?

    Reply

  4. Posted by Kris on June 27, 2013 at 12:57 am

    Nevermind
    When I ran the query I was in MASTER database.

    After switching to my database then it worked.

    Reply

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

%d bloggers like this: