removing constraints without knowing the constraint name

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,

-GaryYou 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" <gld@.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|||Thanks. That works perfectly.

-Gary

"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uXQ32kLzEHA.1392@.TK2MSFTNGP14.phx.gbl...
> 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" <gld@.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