How do Drop Tables in Single Shot

Use this ..

drop table from sys.tables where name like 'tblEMi%'

if Not Done than use below code.

DECLARE @id varchar(200) 
DECLARE @drop varchar(150)
DECLARE @namingPattern varchar(150) 
set @namingPattern = 'tblEMi%'

DECLARE myCursor CURSOR FOR 
    SELECT name FROM sys.tables WHERE name like @namingPattern 

OPEN myCursor 
FETCH next FROM myCursor INTO @id 

WHILE @@fetch_status=0 
BEGIN 
	
    SET @drop = N'drop table ' + @id 
   
    EXECUTE(@drop) 
    
  
    FETCH next FROM myCursor INTO @id 
END 

CLOSE myCursor 
DEALLOCATE myCursor

Thanks
AARIF SHAIKH

Hi Nirmala, before dropping all the tables, you might need to delete all the relations between them or perhaps you need to delete those tables in a proper order. So you can get the list of the all the tables from the below query.

SELECT sySchema.name, sysTab.name FROM sys.tables AS sysTab INNER JOIN sys.schemas AS sySchema 
  ON sysTab.[schema_id] = sySchema.[schema_id] WHERE sysTab.name LIKE 'tblEMi%';

So once you are happy with the table names, you can run the below query to provide you the scripts to drop the tables.

DECLARE @sqlQry NVARCHAR(MAX) = '';
SELECT @sqlQry += 'DROP TABLE ' + QUOTENAME(sySchema.name) + '.' + QUOTENAME(sysTab.name) + ';'
    FROM sys.tables AS sysTab INNER JOIN sys.schemas AS sySchema ON sysTab.[schema_id] = sySchema.[schema_id]
    WHERE sysTab.name LIKE 'tblEMi%';
SELECT @sqlQry;

/* Drop Tables With Certain Prefixes */
declare @cmd varchar(4000)
declare cmds cursor for
select 'drop table [' + Table_Name + ']'
from INFORMATION_SCHEMA.TABLES
where Table_Name like 'prefix%'

open cmds
while 1=1
begin
fetch cmds into @cmd
if @@fetch_status != 0 break
exec(@cmd)
end
close cmds;
deallocate cmds