SQL Server - Get constraints with definitions

List all constraints created in database tables including their definition. Very quick overview of data integrity helpers called constraints.

HINT: Add where clause to focus on particular tables only.

SELECT 
	s.name AS SchemaName,
	o.name AS TableName, 
	c.name AS ConstraintName,
	'CHECK' AS ConstraintType,
	c.definition AS ConstraintDefinition,
	c.create_date AS CreatedAt,
	c.modify_date AS ModifiedAt
FROM sys.check_constraints c
	INNER JOIN sys.objects o ON c.parent_object_id = o.object_id
	INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
UNION
SELECT 
	s.name AS SchemaName,
	o.name AS TableName, 
	c.name AS ConstraintName,
	'DEFAULT' AS ConstraintType,
	c.definition AS ConstraintDefinition,
	c.create_date AS CreatedAt,
	c.modify_date AS ModifiedAt
FROM sys.default_constraints c 
	INNER JOIN sys.objects o ON c.object_id = o.object_id
	INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
SchemaNamesysnameName of the schema.
TableNamesysnameName of table which constraint is created for.
ContraintNamesysnameName of the constraint.
ConstraintTypevarcharType of constraint. Can be DEFAULT or CHECK.
ConstraintDefinitionnvarchar(max)SQL expression that defines this constraint.
CreatedAtdatetimeDate the object was created.
ModifiedAtdatetimeDate the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when an index on the table or view is created or altered.

Leave a Reply

Your email address will not be published. Required fields are marked *