August 17, 2006 at 9:15 am
Need to dynamically DROP a set of tables.... this is not working:
DECLARE @TableName varchar(256),
@command varchar(1000)
DECLARE DropTableCsr CURSOR FOR
SELECT NAME FROM sysobjects
WHERE xtype = 'U' AND name LIKE 'SPCpositions_cs0%' AND datediff(day,crdate,getdate())>400
OPEN DropTableCsr
FETCH NEXT FROM DropTableCsr into @TableName
WHILE @@fetch_status = 0
BEGIN
SELECT @command = 'DROP TABLE ' + @TableName
EXEC @CMD
I also tried: exec sp_executesql @cmd
Any suggestions?
August 17, 2006 at 9:22 am
>>this is not working
Need to be a bit more specific.
What is the actual error you're getting ? Are the tables involved in referential integrity ? You can't drop tables if they have DRI enabled.
August 17, 2006 at 9:27 am
Error:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 10
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
All I want to do is DROP a table dynamically (using a CURSOR to dynamically feed the list of tables to be dropped 1 at a time)
August 17, 2006 at 9:29 am
The error is pretty explicit is it not ?
It's telling you it expects @cmd to be of type 'ntext/nchar/nvarchar', but you've declared it as varchar.
August 17, 2006 at 9:42 am
This worked:
DECLARE @t_schema varchar(500),
@t_name varchar(256)
DECLARE c1 cursor static for
SELECT Name, 'DBO' FROM sysobjects
WHERE xtype = 'U' AND name LIKE 'Rich%' --AND datediff(day,crdate,getdate())>400
OPEN c1
FETCH NEXT FROM c1 INTO @t_name , @t_schema
WHILE @@fetch_status = 0
BEGIN
EXEC ('DROP TABLE ' + @t_schema +'.[' + @t_name + ']' )
FETCH NEXT FROM c1 INTO @t_name , @t_schema
END
CLOSE c1
DEALLOCATE c1
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply