How to list constraints of a table

11 January,2016 by Jack Vamvas

To list sql table constraints you’ll need to use the sys.default_constraints catalogs view. Avoid using the sysobjects view , which is now deprecated.  

The query below includes an INNER JOIN to sys.columns. The purpose is to supply the column information , along with the constraint definition

The query returns the table name, column name, constraint name and constraint definition. There is some other useful information available, if you elect any other columns available.

Some other useful information may be:

Create_date and modify_date

If you want to see the full list of columns , add * to the SELECT statement

 

SELECT 
   st.Name as [TableName],
   co.Name as [ColumnName] ,
   dc.Name as  [ConstraintName],
   dc.definition as [Definition]
FROM sys.tables st
INNER JOIN sys.default_constraints dc ON st.object_id = dc.parent_object_id
INNER JOIN sys.columns co ON dc.parent_object_id = co.object_id AND co.column_id = dc.parent_column_id
WHERE st.Name = 'my_table_name'

 Read More on Constraints

Script to DROP CONSTRAINT and ADD CONSTRAINT for all CHECK CONSTRAINTS

Difference between UNIQUE CONSTRAINT versus UNIQUE INDEX

Modify a SQL CHECK CONSTRAINT - SQL Server

List Foreign Key Constraints -MS SQL - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to list constraints of a table


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer