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

Drop a default constraint without knowing the name « Technical Aspects in IT

Drop a default constraint without knowing the name « Technical Aspects in IT — Tue, 06 Jan 2009 05:48:02 GMT

Pingback from Drop a default constraint without knowing the name « Technical Aspects in IT

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

Rob Farley — Wed, 28 Nov 2007 07:42:22 GMT

Ah, nice tip. Thanks Darren.

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

Darren Gosbell — Tue, 27 Nov 2007 10:00:00 GMT

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