DROP multiple tables 1

[richardy]

Hi,

Is there a mechanism to use the DROP TABLE with multiple tables in one go ie V% or something similar?

Regards,

Richard

 

[fredericofonseca]

something like (untested)

declare @owner sysname
declare @table sysname
declare @cmd nvarchar(500)

declare tcur cursor for
select TABLE_SCHEMA,TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME like 'TEST%'
and TABLE_TYPE = 'BASE TABLE'
and OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'IsMSShipped')=0

open tcur
fetch next from tcur into @owner,@table
while @@fetch_status = 0
begin

   set @cmd = N'DROP TABLE ['+@owner+N'].['+@table+N']'
   exec(@cmd)

   fetch next from tcur into @owner,@table
end

close tcur
deallocate tcur

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[vongrunt]

Try:

exec sp_msForEachTable 'drop table ?', @whereand= ' and name like ''v%'' '

And of course... do it carefully.


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[richardy]

Both worked great - thank you

Regards,

Richard.
PRIOR Analytics UK

http://www.prior-analytics.com

Winners of the LBA Customer Service Award.

 

[amrita418]

As vongrunt mentioned, you should use automated scripts carefully when doing something like dropping a table.

Personally I would prefer to use something like the code below (rather than automate the process):

Run the statement:

select 'Drop Table ' + table_name from information_schema.tables where table_name like 'v%'

Double check the results and run the drop scripts.

Regards,
AA

 

[earthandfire]

fredericofonseca, perfectly timed post.

I needed to be able to create tables where the name is unknown (ie based on a series of variables) and this :

set @cmd = N'DROP TABLE ['+@owner+N'].['+@table+N']'
exec(@cmd)

was the exact example I needed.

Thanks.