how to check a table is empty and if it is empty drop table?

Sujeet Raman

I am trying to check a table is empty and if it is empty drop the table in dynamic sp but my query is not working.2 methods i have tried

I am checking if a coloumn is null or empty will drop the table

set @dropempty='IF EXISTS (SELECT * FROM ' + @table+ ' WHERE city IS NULL OR country= '' '' ) DROP TABLE ' + @table
select @dropempty
EXEC sys.sp_executesql @dropempty

2nd methods row check

set @dropempty='IF EXISTS (SELECT 1 FROM ' + @table+ ''') DROP TABLE ' + @table+''
EXEC sys.sp_executesql @dropempty

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pankajkumar Patel6y ago
Accepted answer
Hi Karthik Kumar,

Following way you can check a table is empty and if it is empty drop table:

After enough tries, I have done with 2nd methods row check:
declare @table nvarchar(25)
set @table = 'cities'

declare @dropempty nvarchar(500)
SET @dropempty = 'IF NOT EXISTS (SELECT 1 FROM ' + @table + ') BEGIN DROP TABLE ' + @table + ' END'

exec (@dropempty)
Output:
It will drop table if there is no records

For more reference:

https://stackoverflow.com/questions/27710260/sql-if-exists-dynamic-query

Hope, this will help you!
Laxmidhar Sahoo6y ago
hi
set @dropempty="IF NOT EXISTS (SELECT * FROM " + @table+ " WHERE city IS NULL OR country= 'xyz') "

EXEC (@dropempty)
If @@RowCount = 0
EXEC("DROP TABLE " + @table  )
Thanks and regards
Nisha Regil6y ago
Try executing the if exists query individually. Does it return error?
Sujeet Raman6y ago
i declared and cheking here if

set @dropempty='IF EXISTS (SELECT 1 FROM ' + @table+ ''') DROP TABLE ' + @table+''
EXEC sys.sp_executesql @dropempty
Nisha Regil6y ago
Check that your IF EXISTS condition is true.

Try with declaring @dropempty as NVarchar