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

November 26, 2007

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).

This Post Has 2 Comments

  1. Darren Gosbell

    The schema is the issue with the powershell script. I thought I would have been able to pipe the Tables collection to get-member to see it’s methods, but PowerShell was too smart and pulled out the first table so I ended up using reflector to find out that the TableCollection class has an Item() method which has an overload that takes 2 strings, the name and the schema.

    So the following should work.

    [reflection.assembly]::LoadwithPartialName(“Microsoft.SQLServer.SMO”)
    $sql = New-Object ‘Microsoft.sqlserver.management.smo.server’ ‘localhost’
    $sql.Databases[“AdventureWorks”].Tables.Item(“Department”,”HumanResources”).Columns[“ModifiedDate”].DefaultConstraint.Drop()

Leave a Reply

LobsterPot Blogs

Blog posts by Rob Farley and other LobsterPot Solutions team members.

Search