• Home
  • About
SQiLler

Tag Archives: drop table

Drop table if exists

Posted on July 26, 2014 by Hiram Romero

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')
Posted in SQL | Tags: drop table, function, MSSQL, sql, sqlserver, stored procedure, TSQL | Leave a comment |

Stack Overflow

profile for sqiller on Stack Exchange, a network of free, community-driven Q&A sites

Recent Posts

  • English dictionary with synonyms
  • Create your own SQL log
  • Drop table if exists
  • Replace a value in all tables
  • Share temp tables to other sessions
Follow on Bloglovin

Pages

  • About

Archives

  • June 2015
  • August 2014
  • July 2014

Categories

  • SQL (23)

WordPress

  • Register
  • Log in
  • WordPress
CyberChimps

CyberChimps

© SQiLler