November 14, 2003 at 4:41 am
I am trying to create a script to remove unused columns in a large database.
Doesn't alter table support variables?
I am running SQL Server 2000.
Here is my code which gets a syntax error in the alter table line.
>Declare @t varchar (50)
>Declare tbl_cur cursor for
>select name from syscolumns where name >like '%_price'
>
>OPEN tbl_cur
>FETCH NEXT from tbl_cur INTO @t
>WHILE @@FETCH_STATUS = 0
>BEGIN
> ALTER TABLE nbd_main DROP COLUMN @t
>FETCH NEXT from tbl_cur INTO @t
>END
>
>CLOSE tbl_cur
>DEALLOCATE tbl_Cur
Thanx for any information
------------------------------------------
Did you know that if you don't drink your milk every morning, the Arla-rabbit will come and shoot you in the head, or sometimes in the belly?
November 14, 2003 at 4:49 am
I think you must execute this dynamically. So something like this should work
DECLARE @stmt nvarchar(100) --Additional declaration
Replace your ALTER TABLE statement with this
SET @stmt = N'ALTER TABLE nbd_main DROP COLUMN @t
EXEC sp_executeSQL @stmt
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 14, 2003 at 4:59 am
Thanks a lot. It works fine now.
------------------------------------------
Did you know that if you don't drink your milk every morning, the Arla-rabbit will come and shoot you in the head, or sometimes in the belly?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply