Drop an unnamed constraint from SqlServer 2000

frost137 (Douglas Wyatt) — Wed, 12 Mar 2008 22:00:43 GMT

// description of your code here



declare @name nvarchar(32), 

    @sql nvarchar(1000)



-- find constraint name

select @name = O.name 

from sysobjects AS O

left join sysobjects AS T

    on O.parent_obj = T.id

where isnull(objectproperty(O.id,'IsMSShipped'),1) = 0

    and O.name not like '%dtproper%'

    and O.name not like 'dt[_]%'

    and T.name = 'MyTable'

    and O.name like 'DF__MyTable__MyColu%'



-- delete if found

if not @name is null

begin

    select @sql = 'ALTER TABLE [MyTable] DROP CONSTRAINT [' + @name + ']'

    execute sp_executesql @sql

end



-- do your ALTER TABLE here



-- replace the constraint

select @sql = 'ALTER TABLE [MyTable] ADD CONSTRAINT [' + @name + '] DEFAULT (0) FOR [MyColumn]'

execute sp_executesql @sql

DZone Snippets: SQLServer code

Drop an unnamed constraint from SqlServer 2000