Getting all the constraints for a Table

How to get all the constraints for a table?

 

Sysconstraints table –

This is the table which contains the constraints which are defined on various tables.

If you query by – SELECT * FROM sysconstraints

You will get OUTPUT –

constid     id          colid  spare1 status      actions     error

----------- ----------- ------ ------ ----------- ----------- ------

2099048     2133582639  6      0      2069        4096        0

11147085    1269579561  2      0      2067        4096        0

14623095    2130106629  6      0      2069        4096        0

27147142    1301579675  2      0      2067        4096        0

For details go to http://msdn2.microsoft.com/en-us/library/aa260399(sql.80).aspx

 

Now come to the actual point – getting all constraints for a table

 

SELECT OBJECT_NAME(constid) 'Constraint Name',

  constid 'Constraint ID',

  CASE (status & 0xF)

    WHEN 0x1 THEN 'Primary Key'

    WHEN 0x2 THEN 'Unique'

    WHEN 0x3 THEN 'Foreign Key'

    WHEN 0x4 THEN 'Check'

    WHEN 0x5 THEN 'Default'

    ELSE 'Undefined'

  END 'Constraint Type',

  CASE (status & 0x30)

    WHEN 0x10 THEN 'Column Level'

    WHEN 0x20 THEN 'Table Level'

    ELSE 'Unknown'

  END 'Constraint Level'

  FROM sysconstraints

  WHERE id=OBJECT_ID(‘TABLE_NAME’)

 

This will result to all the constraints applied to this table.