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

The constraint information is in sysconstraints system object. The sysconstraints.constid is the object_id of the constraints object in sys.objects table and 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.



Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook