1

I have a database in SQL Server where there are many similar tables such as dbo.Dos_150602_xyz. I tried to delete only the tables with 1506 in them by typing:

drop table dbo.Dos_1506*;

But that didn't work. How else can I perform this?

Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ToofanHasArrived
  • 85
  • 1
  • 9
  • 1
    This [link](http://stackoverflow.com/questions/4393/drop-all-tables-whose-names-begin-with-a-certain-string) might be helpful. – Kryptonian Aug 19 '15 at 17:26
  • @learningNew shared a useful link this will generate a script which will also help you to cross check your desire table to DROP it – wiretext Aug 19 '15 at 17:38
  • The script will do all the work, just modify/add the conditions - `where Table_Name like '%1506%' and TABLE_TYPE = 'BASE TABLE'` – Abhishek Aug 20 '15 at 07:33

1 Answers1

2

Just to make things a bit easier for the OP.

Sample table creation script: create table table_pattern_name_1 ( s1 varchar(20), n1 int ); create table table_pattern_name_2 ( s1 varchar(20), n1 int ); create table table_pattern_name_3 ( s1 varchar(20), n1 int ); create table table_pattern_name_4 ( s1 varchar(20), n1 int );

Table Drop script: declare @cmd varchar(4000) declare cmds cursor for select 'drop table [' + Table_Name + ']' from INFORMATION_SCHEMA.TABLES where Table_Name like 'table_pattern_name_%' open cmds while 1=1 begin fetch cmds into @cmd if @@fetch_status != 0 break print @cmd exec(@cmd) end close cmds; deallocate cmds

SSMS Output: drop table [table_pattern_name_1] drop table [table_pattern_name_2] drop table [table_pattern_name_3] drop table [table_pattern_name_4]

Let me know if this works for your example.

Thanks, Matt Jones Microsoft SQL Server

Matt Jones MSFT
  • 59
  • 7