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
