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:

/* The Check Constraints */--all the check constraints in the database SELECTob.name AS Check_Constraint_Name,OBJECT_SCHEMA_NAME(ob.parent_object_id) + '.' + OBJECT_NAME(ob.parent_object_id) AS Parent_Object_Name,COALESCE(ep.value,'') AS DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS epON ep.major_id = ob.object_idAND ep.class=1AND ep.name='MS_Description'--the microsoft convention WHERE OBJECTPROPERTY(ob.object_id, 'IsCheckCnst') = 1

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 ,CASEWHEN 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 Contraint_Column_Name,definition AS Contraint_Code--the code that does the constraint FROM sys.check_constraints ccORDER BYTable_Name,Check_Constraint_Name

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

--get constraint 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,CASEWHEN COUNT(*) = 1 THEN MAX(column_Name)ELSECOALESCE(STUFF((SELECT ', ' + multi.COLUMN_NAMEFROM Information_Schema.CONSTRAINT_COLUMN_USAGE multiWHERE multi.Table_schema=cc.Table_schemaAND multi.constraint_name=scc.constraint_nameFOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,2,''),'?')END AS Constraint_Column_NamesFROM Information_Schema.TABLE_CONSTRAINTS stcINNER JOIN Information_Schema.CHECK_CONSTRAINTS sccON scc.constraint_schema=stc.table_schemaAND scc.constraint_name=stc.Constraint_nameINNER JOIN Information_Schema.CONSTRAINT_COLUMN_USAGE ccON cc.CONSTRAINT_NAME=stc.CONSTRAINT_NAMEAND cc.TABLE_SCHEMA=stc.TABLE_SCHEMAGROUP BYcc.Table_schema,cc.Table_name,scc.Constraint_name,scc.Check_clauseORDER BYcc.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 ConstraintsFROM Information_Schema.CONSTRAINT_TABLE_USAGEGROUP BY table_schema, table_Name, table_schema + '.' + table_NameORDER BYCOUNT(*)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,CASEWHEN OBJECTPROPERTYEX(c.object_ID,'CnstIsClustKey') = 1 THEN 'Clustered'ELSE 'NonClustered'END,CASEWHEN OBJECTPROPERTYEX(c.object_ID,'CnstlsDisabled’') = 1 THEN 'Disabled'ELSE 'Enabled'END AS Index_TypeFROM sys.key_constraints cORDER BY Table_Name

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

--get all constraints for all user tables

--replace parameter value if only a specific constraint type is required

DECLARE @ConstraintType SYSNAMESELECT @ConstraintType = 'IsConstraint'/* IsConstraint --for all constraint types 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 oWHERE OBJECTPROPERTYEX(object_id, @ConstraintType) = 1ORDER BYTable_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 DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS epON ep.major_id = ob.object_idAND ep.class = 1AND ep.name = 'MS_Description' --the microsoft convention WHERE OBJECTPROPERTY(ob.object_id, 'IsConstraint') = 1;

Get Detail List of all Default Constraints in a Database:

/* The Default Constraints */

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

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 dcORDER BYTable_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 SELECTob.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 DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS epON ep.major_id = ob.object_idAND ep.class = 1AND ep.name = 'MS_Description' --the microsoft convention LEFT OUTER JOIN sys.extended_properties AS EP_parentON ep.major_id = ob.parent_object_idAND 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 SELECTob.name AS Default_Name --see all Sybase-style defaults ,COALESCE(ep.value, '') AS DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS epON ep.major_id = ob.object_idAND ep.class = 1AND 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 SELECTob.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 DocumentationFROM sys.objects AS obLEFT OUTER JOIN sys.extended_properties AS EPON EP.major_id = ob.object_idAND EP.name = 'MS_Description' --the microsoft convention LEFT OUTER JOIN sys.extended_properties AS EPParentON EPParent.major_id = ob.parent_object_idAND EPParent.minor_id = 0AND EPParent.name = 'MS_Description' --the microsoft convention WHERE OBJECTPROPERTY(ob.object_id,'IsUniqueCnst')= 1;