Two ways to find / drop a default constraint without knowing its name

So suppose you’ve created a default constraint without specifying the name (on a SQL Server 2005 machine). You do know the table and column, of course – you just don’t have the name of the default constraint. Now you want to find the name so that you can drop the constraint.

A nice query against the system catalog views can help. Something like:

declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
set @table_name = N’Department’
set @col_name = N’ModifiedDate’

select t.name, c.name, d.name, d.definition
from sys.tables t
    join
    sys.default_constraints d
        on d.parent_object_id = t.object_id
    join
    sys.columns c
        on c.object_id = t.object_id
        and c.column_id = d.parent_column_id
where t.name = @table_name
and c.name = @col_name

–should do the trick. If you want to then create some dynamic SQL to do the remove, that’s quite easy too.

Alternatively, if you’d rather use PowerShell, you could do something like:

[reflection.assembly]::LoadwithPartialName(“Microsoft.SQLServer.SMO”)
$sql = New-Object ‘Microsoft.sqlserver.management.smo.server’ ‘localhost’
($sql.Databases[“AdventureWorks”].Tables | where-object -filter {$_.Name -eq “Department”}).Columns[“ModifiedDate”].DefaultConstraint.Drop()

This is using SMO to jump to the appropriate database, find the table called Department, grab the appropriate column out of it and drop the default constraint on it. I’m not sure why I couldn’t just use $sql.Databases[“AdventureWorks”].Tables[“Department”].Columns[“… I guess there’s something I’m not quite getting there. Possibly something about schemas, because I can use this method if the table is in the dbo schema (possibly read ‘default’ there).