drop_tables_with_zero_rows.sql

--works only for small databases with ~ < 200 tables...

declare @dropsql varchar(max) = ''
declare @tablename varchar(100)

declare tablecursor cursor for   
select 
	t.name
 from  sys.dm_db_partition_stats s
inner join sys.tables t on t.object_id = s.object_id
where 
s.row_count = 0 
and  
(index_id = 0 
or 
index_id = 1)
group by t.name, s.row_count

open tablecursor  

fetch next from tablecursor   
into @tablename

while @@fetch_status = 0  
begin   
	set @dropsql = @dropsql + ' drop table ' + @tablename + ';' + char(13)
	fetch next from tablecursor into @tablename  
	
end
close tablecursor;  
deallocate tablecursor; 

print @dropsql
--execute @dropsql