Credits:
Description
- 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
Code
/* 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 |
--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 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 |
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 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 |
--get all constraints for all user tables --replace parameter value if only a specific constraint type is required |
/* 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; |
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 |
/* 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; |
/* 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; |
/* 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; |