Get Constraints Metadata

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested

Credits:

Author:Phil Factor/ChillyDBA

Date: 3 Jun 2020

Description

A small collection of queries to produce database-level reports on constraints. These should mostly run as-is but can be easily customized.

There are 2 main types of report queries:

• 1. Summary List - containing schema/object names and any stored extended properties. Ideal for documentation

  2. Detail List - containing schema/object names plus object type, component columns and code. Can be used for documentation, but also valuable for use when constructing Dynamic SQL queries

Where there are multiple variants on the same query, one will utilize the INFORMATION_SCHEMA views and the other the Dynamic Management Views. This is generally for Detail type queries as some detail levels are only available in one source

Code

Get Summary List of Check Constraints in a Database:

Get Detail List of all Check Constraints in a Database (1):

--list all check constraints

SELECT

@@SERVERNAME AS Server_Name

,DB_NAME() AS Database_Name

,OBJECT_SCHEMA_NAME(cc.Parent_Object_ID) AS Schema_Name

,OBJECT_NAME(cc.Parent_Object_ID) AS Table_Name

,OBJECT_SCHEMA_NAME(cc.Parent_Object_ID) +'.' + OBJECT_NAME(cc.Parent_Object_ID) AS Combined_Name

,name AS Check_Constraint_Name --the name of the check constraint

,CASE

WHEN parent_column_id > 0 THEN COL_NAME(Parent_Object_ID,parent_column_id) --column constraint

ELSE '(Table)' --0 means that it is a table constraint

END AS Constraint_Column_Name

,definition AS Constraint_Code--the code that does the constraint

FROM sys.check_constraints cc

ORDER BY

Table_Name

,Check_Constraint_Name

Get Detail List of all Check Constraints in a Database (2):

--get constrain details for table and column constraints with columns

SELECT

@@SERVERNAME AS Server_Name

,DB_NAME() AS Database_Name

,cc.TABLE_SCHEMA AS Schema_Name

,cc.TABLE_NAME AS Table_Name

,cc.TABLE_SCHEMA +'.' + cc.TABLE_NAME AS Combined_Name

,scc.CONSTRAINT_NAME AS Check_Constraint_Name

,Check_clause AS Contraint_Code

,CASE

WHEN COUNT(*) = 1 THEN MAX(column_Name)

ELSE

COALESCE

(

STUFF

(

(

SELECT ', ' + multi.COLUMN_NAME

FROM Information_Schema.CONSTRAINT_COLUMN_USAGE multi

WHERE multi.Table_schema=cc.Table_schema

AND multi.constraint_name=scc.constraint_name

FOR XML PATH (''), TYPE

).value('.', 'varchar(max)'),1,2,''

),'?'

)

END AS Constraint_Column_Names

FROM Information_Schema.TABLE_CONSTRAINTS stc

INNER JOIN Information_Schema.CHECK_CONSTRAINTS scc

ON scc.constraint_schema=stc.table_schema

AND scc.constraint_name=stc.Constraint_name

INNER JOIN Information_Schema.CONSTRAINT_COLUMN_USAGE cc

ON cc.CONSTRAINT_NAME=stc.CONSTRAINT_NAME

AND cc.TABLE_SCHEMA=stc.TABLE_SCHEMA

GROUP BY

cc.Table_schema

,cc.Table_name

,scc.Constraint_name

,scc.Check_clause

ORDER BY

cc.Table_name

,scc.Constraint_name

Get Count of Check Constraints per table in a Database:

SELECT

@@SERVERNAME AS Server_Name

,DB_NAME() AS Database_Name

,table_schema AS Schema_Name

,table_Name AS Table_Name

,table_schema + '.' + table_Name AS Combined_Name

,COUNT(*) AS Constraints

FROM Information_Schema.CONSTRAINT_TABLE_USAGE

GROUP BY table_schema, table_Name, table_schema + '.' + table_Name

ORDER BY

COUNT(*)DESC

Determine the type of index used to enforce all Check Constraints in a Database:

--determine what type of index is being used to enforce constraints

SELECT

@@SERVERNAME AS Server_Name

,DB_NAME() AS Database_Name

,OBJECT_SCHEMA_NAME(c.parent_object_id) AS Schema_Name

,OBJECT_NAME(c.parent_object_id) AS Table_Name

,OBJECT_SCHEMA_NAME(c.parent_object_id) +'.' + OBJECT_NAME(c.parent_object_id) AS Combined_Name

,CONVERT(CHAR(40),c.Name) AS Constraint_name

,CASE

WHEN OBJECTPROPERTYEX(c.object_ID,'CnstIsClustKey') = 1 THEN 'Clustered'

ELSE 'NonClustered'

END

,CASE

WHEN OBJECTPROPERTYEX(c.object_ID,'CnstlsDisabled’') = 1 THEN 'Disabled'

ELSE 'Enabled'

END AS Index_Type

FROM sys.key_constraints c

ORDER BY Table_Name

Summary List of all Constraints in a Database with type (1):

--get all constraints for all user tables

DECLARE @ConstraintType SYSNAME

SELECT @ConstraintType = 'IsConstraint'

/*

IsConstraint --for all contraint tipyes

IsPrimaryKey --|

IsForeignKey --|

IsDefaultCnst --| -- for specific constraint types

IsCheckCnst --|

IsUniqueCnst --|

*/

SELECT

@@SERVERNAME AS Server_Name

,DB_NAME() AS Database_Name

,OBJECT_SCHEMA_NAME(o.parent_object_ID) AS Schema_Name

,OBJECT_NAME(o.parent_object_ID) AS Table_Name

,CONVERT(CHAR(50),OBJECT_SCHEMA_NAME(o.parent_object_ID)+'.' + OBJECT_NAME(o.parent_object_ID)) AS Combined_Name

,Name AS Constraint_Name

,LOWER(REPLACE(type_desc,'_',' ')) AS Constraint_Type --the type of constraint

FROM sys.objects o

WHERE OBJECTPROPERTYEX(object_id, @ConstraintType) = 1

ORDER BY

Table_Name

,Constraint_Type

,Constraint_Name

Summary List of all Constraints in a Database with type (2):

/* The Constraints */

SELECT

--all the constraints in the database

@@SERVERNAME AS Server_Name

,DB_NAME() AS Database_Name

,ob.name AS Constraint_Name --see all constraints and parent table

,LOWER(REPLACE(type_desc,'_',' ')) AS Constraint_Type --the type of constraint

,OBJECT_SCHEMA_NAME(ob.parent_object_id) + '.' + OBJECT_NAME(ob.parent_object_id) AS Parent_Object_Name

,COALESCE(ep.value, '') As Documentation

FROM sys.objects AS ob

LEFT OUTER JOIN sys.extended_properties AS ep

ON ep.major_id = ob.object_id

AND ep.class = 1

AND ep.name = 'MS_Description' --the microsoft convention

WHERE OBJECTPROPERTY(ob.object_id, 'IsConstraint') = 1;

Get Detail List of all Default Constraints in a Database:

SELECT

@@SERVERNAME AS Server_Name

,DB_NAME() AS Database_Name

,OBJECT_SCHEMA_NAME(dc.Parent_Object_ID) AS Schema_Name

,OBJECT_NAME(dc.Parent_Object_ID) AS Table_Name

,OBJECT_SCHEMA_NAME(dc.Parent_Object_ID) +'.' + OBJECT_NAME(dc.Parent_Object_ID) AS Combined_Name

,name AS Default_Constraint_Name --the name of the check constraint

,COL_NAME(Parent_Object_ID, parent_column_id) AS Constraint_Column_Name

,definition AS Constraint_Definition --the code that does the constraint

FROM sys.default_constraints dc

ORDER BY

Table_Name

,Constraint_Column_Name

Get Summary List of all Default Constraints in a Database:

/* The Default Constraints */

--all default constraints in the database and their parent table

SELECT

ob.name AS Default_Constraint_Name--see all Default constraints and parent table

,COALESCE(ep.value,'') AS Documentation

,OBJECT_SCHEMA_NAME(ob.parent_object_id)+'.'+OBJECT_NAME(ob.parent_object_id) AS Parent_Object_Name

,COALESCE(EP_parent.value,'') AS Documentation

FROM sys.objects AS ob

LEFT OUTER JOIN sys.extended_properties AS ep

ON ep.major_id = ob.object_id

AND ep.class = 1

AND ep.name = 'MS_Description' --the microsoft convention

LEFT OUTER JOIN sys.extended_properties AS EP_parent

ON ep.major_id = ob.parent_object_id

AND ep.name = 'MS_Description' --the microsoft convention

WHERE OBJECTPROPERTY(ob.object_id,'IsDefaultCnst')= 1;

Get Summary List of all old-style Column Default Constraints in a Database:

/* The Defaults */

--all old-style defaults in the database

SELECT

ob.name AS Default_Name --see all Sybase-style defaults

,COALESCE(ep.value, '') AS Documentation

FROM sys.objects AS ob

LEFT OUTER JOIN sys.extended_properties AS ep

ON ep.major_id = ob.object_id

AND ep.class = 1

AND ep.name = 'MS_Description' --the microsoft convention

WHERE OBJECTPROPERTY(ob.object_id, 'IsDefault') = 1;

Get Summary List of all Unique Constraints in a Database:

/* The Unique Constraints */

--all unique constraints in the database and their parent table

SELECT

ob.name AS Unique_Constraint_Name--all unique constraints

,OBJECT_SCHEMA_NAME(ob.parent_object_id)+'.'+ OBJECT_NAME(ob.parent_object_id) AS Parent_Object_Name

,COALESCE(EP.value, '') AS ConstraintDoc, COALESCE(EPParent.value, '') AS Documentation

FROM sys.objects AS ob

LEFT OUTER JOIN sys.extended_properties AS EP

ON EP.major_id = ob.object_id

AND EP.name = 'MS_Description' --the microsoft convention

LEFT OUTER JOIN sys.extended_properties AS EPParent

ON EPParent.major_id = ob.parent_object_id

AND EPParent.minor_id = 0

AND EPParent.name = 'MS_Description' --the microsoft convention

WHERE OBJECTPROPERTY(ob.object_id,'IsUniqueCnst')= 1;