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.
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
where Table_Name like '%1506%' and TABLE_TYPE = 'BASE TABLE'