How to Find a Default Constraint Using Information_Schema

How do I find a default constraint using INFORMATION_SCHEMA?

As I understand it, default value constraints aren't part of the ISO standard, so they don't appear in INFORMATION_SCHEMA. INFORMATION_SCHEMA seems like the best choice for this kind of task because it is cross-platform, but if the information isn't available one should use the object catalog views (sys.*) instead of system table views, which are deprecated in SQL Server 2005 and later.

Below is pretty much the same as @user186476's answer. It returns the name of the default value constraint for a given column. (For non-SQL Server users, you need the name of the default in order to drop it, and if you don't name the default constraint yourself, SQL Server creates some crazy name like "DF_TableN_Colum_95AFE4B5". To make it easier to change your schema in the future, always explicitly name your constraints!)

-- returns name of a column's default value constraint 
SELECT
    default_constraints.name
FROM 
    sys.all_columns

        INNER JOIN
    sys.tables
        ON all_columns.object_id = tables.object_id

        INNER JOIN 
    sys.schemas
        ON tables.schema_id = schemas.schema_id

        INNER JOIN
    sys.default_constraints
        ON all_columns.default_object_id = default_constraints.object_id

WHERE 
        schemas.name = 'dbo'
    AND tables.name = 'tablename'
    AND all_columns.name = 'columnname'

SQL default value constraint INFORMATION_SCHEMA

Please use the following query to find the default constraint for a every table.

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc = 'DEFAULT_CONSTRAINT'

How to check the column default constraint exist?

the query

select COLUMN_DEFAULT 
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @tableName
and COLUMN_NAME = @columnName

will return the default value specified for a column (null if no default was defined)

---

Update:

The following query will also retrieve the constraint's name:

select 
    dc.name,
    dc.definition
from sys.default_constraints dc
join sys.objects o
    on o.object_id = dc.parent_object_id
join sys.columns c
    on o.object_id = c.object_id
    and c.column_id = dc.parent_column_id
where o.name = @tableName
and c.name = @columnName

If no rows are returned, then there's no default constraint defined on the column.

How to check if the Default Value Constraint exists?

if not exists (
    select *
      from sys.all_columns c
      join sys.tables t on t.object_id = c.object_id
      join sys.schemas s on s.schema_id = t.schema_id
      join sys.default_constraints d on c.default_object_id = d.object_id
    where t.name = 'table'
      and c.name = 'column'
      and s.name = 'schema')
  ....

Getting Default Constraints Information

Default value is in column 'definition', try to run query without where:

SELECT  c.name , 
        col.name, 
        c.definition
FROM    Rem.sys.default_constraints c 
        INNER JOIN Rem.sys.columns col ON col.default_object_id = c.object_id 
        INNER JOIN Rem.sys.objects o ON o.object_id = c.parent_object_id 
        INNER JOIN Rem.sys.schemas s ON s.schema_id = o.schema_id 

SQL default constraint not listed in sys.default_constraints

If the default is showing in INFORMATION_SCHEMA.COLUMNS as something like create default X as Y then it exists as part of a bound default, a deprecated means of applying default values.

It will not show up in sys.default_constraints because it isn't part of the more "modern" constraint system.

---

Related Topics