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')