T-SQL find constraints on table

Many a times need arises to find all constraints on a table. A common use case is when dropping columns from a table we need to know the dependent objects so as to drop them first before dropping a column. Another use case can be of to maintain metadata/inventory of a database. A T-SQL find constraints on table is shown below

SELECT obj_table.NAME      AS TableName, 
       obj_Constraint.NAME AS ConstraintName, 
       columns.NAME        AS ColumnName 
FROM   sys.objects obj_table 
       JOIN sys.objects obj_Constraint 
         ON obj_table.object_id = obj_Constraint.parent_object_id 
       JOIN sys.sysconstraints constraints 
         ON constraints.constid = obj_Constraint.object_id 
       JOIN sys.columns columns 
         ON columns.object_id = obj_table.object_id 
            AND columns.column_id = constraints.colid 
ORDER  BY tablename

The constraint information is in sysconstraints system object. The sysconstraints.constid is the object_id of the constraints object in sys.objects table and sysconstraints.id is the object_id of the table that the constraints belong too. Thus joining sys.objects with sysconstraints based on above information, lists out all the constraint for a table. The sysconstraints.colid maps the column to the constraint. Thus joining sys.columns.column_id with sysconstraints.colid gives information about the columns a constraint refers too. The output from above query is shown below.

 
Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook

Leave a Reply

Your email address will not be published. Required fields are marked *