MS SQL Server - How to DROP COLUMN with a unamed default...

Author

How to DROP COLUMN with a unamed default...

James Hunter Ross

2005-08-01, 11:23 am

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

ChrisR

2005-08-01, 1:23 pm

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
>

James Hunter Ross

2005-08-01, 1:23 pm

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

Razvan Socol

2005-08-01, 1:23 pm

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

Sponsored Links