You can get rid of Defaults with automatically named constraints in a script
with the following bit of code. Just replace the <table name> and <column
names> with your table and column(s):
DECLARE @constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @constraint_name)
END
--
Jacco Schalkwijk
SQL Server MVP
"Gary" <gl*@hotmail.com> wrote in message
news:EIJmd.349072$wV.15308@attbi_s54...
I have the need to remove a constraint on a table since I'm trying to alter
the datatype of one of the columns. I know I can drop the constraint given
the name, but since the name is auto generated (something like
DF__WHRPT_ITV__Expor__45F365D3)
I need a way to find this constraint name so that I can programmatically
remove it.I can get the name using sp_helpconstraint on the table, but
can't
seem to locate where the actual constraint_name is stored.
This is on SQL 2000.
Any help is appreciated.
Thanks,
-Gary