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.nameFROM     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_idWHERE         schemas.name = 'dbo'    AND tables.name = 'tablename'    AND all_columns.name = 'columnname'

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 con o.id = c.cdefaultinner join sysobjects ton c.id = t.idwhere o.xtype = 'D'and c.name = 'Column_Name'and t.name = 'Table_Name'

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

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