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:
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| SchemaName | sysname | Name of the schema. |
| TableName | sysname | Name of table which constraint is created for. |
| ContraintName | sysname | Name of the constraint. |
| ConstraintType | varchar | Type of constraint. Can be DEFAULT or CHECK. |
| ConstraintDefinition | nvarchar(max) | SQL expression that defines this constraint. |
| CreatedAt | datetime | Date the object was created. |
| ModifiedAt | datetime | Date 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. |