Thursday, March 22, 2012

How to list all the constraints in SQL Server Database

 

There are many methods to lists the constraints in SQL database. We will go through few methods one by one.

Method 1:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Above query will return all the constraints in all the database.

Method 2:

SELECT * FROM [DataBaseName].INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Above query will return all the constraints in the specific database.

Method 3:

SELECT OBJECT_NAME(OBJECT_ID) AS [Constraint_Name],
OBJECT_NAME(parent_object_id) AS [TableName],
type_desc AS [TypeOfConstraint]
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'

This method will give bit more details about the constraints. This can be used to return all constraints in the database with Constraint Name, Table Name and Type of Constraint.

Method 4:

sp_help TableName

This query will return all the constraints in the specific table.

No comments: