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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

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