Drop tables where name matches a pattern

I have an arbitrary number of tables in a SQL2000 db with names matching
'MyTable_%'.
Is there a simple way of dropping these with a SQL command?
LMcPheeThere is an undocumented and unsupported way:
EXEC sp_msForEachTable 'IF ''?'' LIKE ''MyTable[_]%'' DROP TABLE ?;';
You should probably test this in a restored backup before you do it to your
production database (though this sounds like a very peculiar thing to do to
a production database, anyway).
As I mentioned yesterday, this will only work if none of these tables have
foreign keys referencing them OR if the cursor in sp_msforeachtable just
happens to process the tables in dependency order.
A safer way is to probably generate the script and execute it manually, this
will allow you to inspect the script and make any necessary changes first.
Using Results in Text in Query Analyzer, run:
SET NOCOUNT ON;
SELECT 'DROP TABLE '+TABLE_NAME +';'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'MyTable[_]%';
Now you can copy the output, paste it to the top pane or a new query editor
window, and run it.
A
"lmcphee" <lmcphee@.discussions.microsoft.com> wrote in message
news:63D0D021-F83D-4DF1-ABD7-EBD2F3EC3074@.microsoft.com...
>I have an arbitrary number of tables in a SQL2000 db with names matching
> 'MyTable_%'.
> Is there a simple way of dropping these with a SQL command?
> LMcPhee|||Try,
-- non documented sp
exec sp_msforeachtable 'if parsename(''?'', 1) like ''my_table%'' drop table
?'
go
-- using a select statement to copy, paste and execute the result
select 'drop table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' + char(13)
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME like 'MyTable_%'
go
-- using a cursor and dynamic sql
declare @.sql nvarchar(4000)
declare my_cursor cursor
local
fast_forward
for
select 'drop table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' as sql
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME like 'MyTable_%'
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.sql
if @.@.error != 0 or @.@.fetch_status != 0 break
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
go
AMB
"lmcphee" wrote:

> I have an arbitrary number of tables in a SQL2000 db with names matching
> 'MyTable_%'.
> Is there a simple way of dropping these with a SQL command?
> LMcPhee