How do I find a default constraint using INFORMATION_SCHEMA?

Question

I'm trying to test if a given default constraint exists. I don't want to use the sysbojects table, but the more standard INFORMATION_SCHEMA.

I've used this to check for tables and primary key constraints before, but I don't see default constraints anywhere.

Are they not there? (I'm using MS SQL Server 2000).

EDIT: I'm looking to get by the name of the constraint.

Answer 1

Is the COLUMN_DEFAULT column of INFORMATION_SCHEMA.COLUMNS what you are looking for?

Answer 2

I don't think it's in the INFORMATION_SCHEMA - you'll probably have to use sysobjects or related deprecated tables/views.

You would think there would be a type for this in INFORMATION_SCHEMA.TABLE_CONSTRAINTS, but I don't see one.

Answer 3

Probably because on some of the other SQL DBMSs the "default constraint" is not really a constraint, you'll not find its name in "INFORMATION_SCHEMA.TABLE_CONSTRAINTS", so your best bet is "INFORMATION_SCHEMA.COLUMNS" as others have mentioned already.

(SQLServer-ignoramus here)

The only a reason I can think of when you have to know the "default constraint"'s name is if SQLServer doesn't support "ALTER TABLE xxx ALTER COLUMN yyy SET DEFAULT..." command. But then you are already in a non-standard zone and you have to use the product-specific ways to get what you need.

Answer 4

There seems to be no Default Constraint names in the Information_Schema views.

use SELECT * FROM sysobjects WHERE xtype = 'D' AND name = @name to find a default constraint by name

Answer 5

You can use the following to narrow the results even more by specifying the Table Name and Column Name that the Default Constraint correlates to:

select * from sysobjects o 
inner join syscolumns c
on o.id = c.cdefault
inner join sysobjects t
on c.id = t.id
where o.xtype = 'd'
and c.name = 'Column_Name'
and t.name = 'Table_Name'

Answer 6

How about using a combination of CHECK_CONSTRAINTS and CONSTRAINT_COLUMN_USAGE:

    select columns.table_name,columns.column_name,columns.column_default,checks.constraint_name
          from information_schema.columns columns
          inner join information_schema.constraint_column_usage usage on 
         columns.column_name = usage.column_name and columns.table_name = usage.table_name
       inner join information_schema.check_constraints checks on usage.constraint_name = checks.constraint_name
    where columns.column_default is not null

Answer 7

select c.name, col.name from sys.default_constraints c inner join sys.columns col on col.default_object_id = c.object_id inner join sys.objects o on o.object_id = c.parent_object_id inner join sys.schemas s on s.schema_id = o.schema_id where s.name = @SchemaName and o.name = @TableName and col.name = @ColumnName