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

Question

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

Pankaj Patel · 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!

Nisha Regil · Answer

Try executing the if exists query individually. Does it return error?

Sujeet Raman · Answer

i declared and cheking here if set @dropempty= 'IF EXISTS (SELECT 1 FROM ' + @table+ ''') DROP TABLE ' + @table+'' EXEC sys.sp_executesql @ dropempty

Nisha Regil · Answer

Check that your IF EXISTS condition is true. Try with declaring @dropempty as NVarchar

Laxmidhar Sahoo · Answer

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