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 sysobjects 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

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'