Finding defaults constraints

There have been times when I need to know the name of a default constraint. Most often it is because I need to modify a column or table and to do so I must first remove the constraint. The annoying thing is when there isn’t any kind of naming convention on constraints. If you define a constraint such as a varchar column with a default of empty string like DEFAULT(”), then SQL Server will generate a basically random name for the constraint. To find the name of all the constraints in a database you can use this query.

SELECT s.name AS SchemaName, t.name AS TableName,
       c.name AS ColumnName, d.name AS ConstraintName
FROM sys.default_constraints d
INNER JOIN sys.tables t
    ON d.parent_object_id = t.object_id
INNER JOIN sys.schemas s
    ON d.schema_id = s.schema_id
INNER JOIN sys.columns c
    ON t.object_id = c.object_id
    AND d.parent_column_id = c.column_id;

If you need to know the constraints on a single table then just limit based on the name from sys.tables.

SELECT s.name AS SchemaName, t.name AS TableName,
       c.name AS ColumnName, d.name AS ConstraintName
FROM sys.default_constraints d
INNER JOIN sys.tables t
    ON d.parent_object_id = t.object_id
INNER JOIN sys.schemas s
    ON d.schema_id = s.schema_id
INNER JOIN sys.columns c
    ON t.object_id = c.object_id
    AND d.parent_column_id = c.column_id
WHERE t.name = 'Subscriptions';

Bookmark the permalink.

Leave a Reply

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