problem dropping column constraint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

problem dropping column constraint

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2008-01-28 : 15:13:41

Buddies,
I have a bit-typed column that is defined with default value of 1 ( a contraint). The constraint name of the column is always CHANGED whenever the column is drop and then re-created. So, I'd like to use a dynamic script as below to get dynamic name of the column's constraint name.

Anytime, I would like to drop the column, so I must dropped the default-value constraint first for the column. However, I get the error like "Incorrect syntax near '@ctName' "


declare @ColumnNameHavingDefaultValue varchar(100), @ctName as varchar(100)

set @ColumnNameHavingDefaultValue = 'UserActive'

-- find out constraint name
select @ctName=a.name
from sysobjects a
inner join syscolumns b on a.info= b.colid and b.name=@ColumnNameHavingDefaultValue
where a.type='D' and a.name like 'DF__WebUser%' and a.parent_obj=(select object_id('WebUser'))

GO
-- drop constraint --> must change 'name of constraint'
ALTER TABLE WebUser DROP @ctName  -- ERROR HAPPENS AT THIS LINE


GO
-- drop column
ALTER TABLE WebUser DROP column UserActive

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-01-28 : 15:43:21

dynamic sql will help you there..

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-01-28 : 15:48:26

try something like this
declare @sql_string varchar(100)
set @sql_string=
'ALTER TABLE WebUser DROP '+@ctName
exec @sql_string

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources