How to DROP COLUMN with a unamed default...

Friends,
We need to drop a column that also has a "default", but the "default" was
unnamed during creation, so SQL generates a name for it. Since we need to
do this programatically on many customer machines, we cannot assume the name
of the "default object". I imagine we would need to acquire the name of the
dependent "default", then drop it, and then we could drop the column.
Does anybody have a reliable technique for doing this sort of thing? Am I
on the right track?
Thanks in advance for your thoughts,
James
select s1.name
from SysObjects s1
inner join SysObjects s2 on s1.parent_obj = s2.id
where s2.name = 'Table1'
and s1.xtype = 'D'
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:utWUUmrlFHA.576@.TK2MSFTNGP15.phx.gbl...
> Friends,
> We need to drop a column that also has a "default", but the "default" was
> unnamed during creation, so SQL generates a name for it. Since we need to
> do this programatically on many customer machines, we cannot assume the
> name of the "default object". I imagine we would need to acquire the name
> of the dependent "default", then drop it, and then we could drop the
> column.
> Does anybody have a reliable technique for doing this sort of thing? Am I
> on the right track?
> Thanks in advance for your thoughts,
> James
>
|||That's great, thanks! Sadly, I have several unamed "default" values in that
table, and I cannot tell which is which. I really only want to drop the on
that is on the column being dropped. Somewhere there must be information on
which column a constraint applies to. Perhaps it is not useable to mortals
though... Any thoughts?
And, thanks again for replying!
James
|||Hello, James
Try this:
DECLARE @.SQL nvarchar(4000)
SELECT @.SQL='ALTER TABLE '+QUOTENAME(o1.name)
+' DROP '+QUOTENAME(o2.name)
FROM sysobjects o1 INNER JOIN syscolumns c ON c.id=o1.id
INNER JOIN sysobjects o2 ON o2.parent_obj=o1.id AND c.colid=o2.info
WHERE o2.type='D' AND c.name='YourColumn' AND o1.name='YourTable'
--PRINT @.SQL
EXEC (@.SQL)
Razvan