Drop Table | SQiLler

This is just a simple way to save some lines on code, specially when we need to drop and recreate a table.

Dropping a table can be easily done with:

DROP TABLE dbo.MyTable

But if the table doesn’t exists, it will throw an error stopping the execution of your query or stored procedure.

That’s why we need to check if the table exists first:

IF EXISTS (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'My table')
	BEGIN
		DROP TABLE dbo.MyTable
	END

And that becomes a little annoying if you need to drop many tables, so here is a simple stored procedure that will do that for you:

CREATE PROCEDURE dbo.usp_DropifExists
    @table varchar(150)
AS
BEGIN
	DECLARE @SQL varchar(max)
	IF EXISTS (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = @table)
	BEGIN
		SET @SQL = 'DROP TABLE '+@table
		EXEC(@SQL)
	END
END

USE:

EXEC dbo.usp_DropifExists ('MyTable')

Tag Archives: drop table

Drop table if exists