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:
- Summary List - containing schema/object names and any stored extended properties. Ideal for documentation
- 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 SELECT ob.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 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, '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 ,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 Contraint_Column_Name ,definition AS Contraint_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 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 ,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 --replace parameter value if only a specific constraint type is required |
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; |
