Over a million developers have joined DZone.

Drop An Unnamed Constraint From SqlServer 2000

DZone's Guide to

Drop An Unnamed Constraint From SqlServer 2000

Free Resource
// 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
    select @sql = 'ALTER TABLE [MyTable] DROP CONSTRAINT [' + @name + ']'
    execute sp_executesql @sql

-- do your ALTER TABLE here

-- replace the constraint
select @sql = 'ALTER TABLE [MyTable] ADD CONSTRAINT [' + @name + '] DEFAULT (0) FOR [MyColumn]'
execute sp_executesql @sql

Opinions expressed by DZone contributors are their own.


Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.


{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}