Cry How to...

List all constraints on a table

---

Contents

• Where to find information about constraints
• List all default constraints for a table
• List all check constraints for a table
• List all key constraints for a table

---

Where to find information about constraints

Information about constraints is available via three views, each of which provides information about a different type of constraint:

sys.default_constraints

Provides information about default constraints, i.e. default values for columns.

sys.check_constraints

Provides information about check constraints.

sys.key_constraints

Provides information about key constraints.

---

List all default constraints for a table

To list all default constraints for a table:

SELECT
    T.Name as TableName,
    C.Name as ColumnName,
    DC.Name as ConstraintName,
    DC.definition as DefaultValue
FROM sys.tables T
INNER JOIN sys.default_constraints DC ON T.object_id = DC.parent_object_id
INNER JOIN sys.columns C ON DC.parent_object_id = C.object_id AND C.column_id = DC.parent_column_id
WHERE T.Name='MyTable'

Substituting the name of the table of interest for "MyTable" in the above.

---

List all check constraints for a table

To list all check constraints for a table:

SELECT
    T.Name as TableName,
    *
FROM sys.Tables T
INNER JOIN sys.check_constraints CC on T.object_id = CC.parent_object_id
WHERE T.Name='MyTable'

Substituting the name of the table of interest for "MyTable" in the above.

---

List all key constraints for a table

To list all key constraints for a table:

SELECT
    T.Name as TableName,
    KC.Name as ConstraintName,
    KC.Type,
    KC.Type_Desc
FROM sys.tables T
INNER JOIN sys.key_constraints KC on T.object_id = KC.parent_object_id
WHERE T.Name='MyTable';

Substituting the name of the table of interest for "MyTable" in the above.

---

These notes have been tested against SQL Server 2008 R2, SQL Server 2008.