How to drop a table if it exists?

The table name is Scores.

Is it correct to do the following?

IF EXISTS(SELECT * FROM dbo.Scores) DROP TABLE dbo.Scores 

16 Answers

Is it correct to do the following?

IF EXISTS(SELECT * FROM dbo.Scores) DROP TABLE dbo.Scores 

No. That will drop the table only if it contains any rows (and will raise an error if the table does not exist).

Instead, for a permanent table you can use

IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL DROP TABLE dbo.Scores; 

Or, for a temporary table you can use

IF OBJECT_ID('tempdb.dbo.#TempTableName', 'U') IS NOT NULL DROP TABLE #TempTableName; 

SQL Server 2016+ has a better way, using DROP TABLE IF EXISTS …. See the answer by @Jovan.

From SQL Server 2016 you can use

DROP TABLE IF EXISTS dbo.Scores 

Reference: DROP IF EXISTS - new thing in SQL Server 2016

It will be in SQL Azure Database soon.

The ANSI SQL/cross-platform way is to use the INFORMATION_SCHEMA, which was specifically designed to query meta data about objects within SQL databases.

if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Scores' AND TABLE_SCHEMA = 'dbo') drop table dbo.Scores; 

Most modern RDBMS servers provide, at least, basic INFORMATION_SCHEMA support, including: MySQL, Postgres, Oracle, IBM DB2, and Microsoft SQL Server 7.0 (and greater).

Have seen so many that don't really work. when a temp table is created it must be deleted from the tempdb!

The only code that works is:

IF OBJECT_ID('tempdb..#tempdbname') IS NOT NULL --Remove dbo here DROP TABLE #tempdbname -- Remoeve "tempdb.dbo" 

In SQL Server 2016 (13.x) and above

DROP TABLE IF EXISTS dbo.Scores 

In earlier versions

IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL DROP TABLE dbo.Scores; 

U is your table type

Or:

if exists (select * from sys.objects where name = 'Scores' and type = 'u') drop table Scores 

There is an easier way

DROP TABLE IF EXISTS table_name; 

I hope this helps:

begin try drop table #tempTable end try begin catch end catch 

I wrote a little UDF that returns 1 if its argument is the name of an extant table, 0 otherwise:

CREATE FUNCTION [dbo].[Table_exists] ( @TableName VARCHAR(200) ) RETURNS BIT AS BEGIN If Exists(select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = @TableName) RETURN 1; RETURN 0; END GO 

To delete table User if it exists, call it like so:

IF [dbo].[Table_exists]('User') = 1 Drop table [User] 

Simple is that:

IF OBJECT_ID(dbo.TableName, 'U') IS NOT NULL DROP TABLE dbo.TableName 

where dbo.TableName is your desired table and 'U' is type of your table.

IF EXISTS (SELECT NAME FROM SYS.OBJECTS WHERE object_id = OBJECT_ID(N'Scores') AND TYPE in (N'U')) DROP TABLE Scores GO 

I use:

if exists (select * from sys.tables where name = 'tableName' and schema_id = schema_id('dbo')) begin drop table dbo.tableName end 

Make sure to use cascade constraint at the end to automatically drop all objects that depend on the table (such as views and projections).

drop table if exists tableName cascade; 

SQL Server 2016 and above the best and simple one is DROP TABLE IF EXISTS [TABLE NAME]

Ex:

DROP TABLE IF EXISTS dbo.Scores 

if suppose the above one is not working then you can use the below one

IF OBJECT_ID('dbo.Scores', 'u') IS NOT NULL DROP TABLE dbo.Scores; 

If you use long codes and want to write less for temporary table create this procedure:

CREATE PROCEDURE MF_DROP (@TEMP AS VARCHAR(100)) AS EXEC('IF OBJECT_ID(''TEMPDB.DBO.' + @TEMP + ''', ''U'') IS NOT NULL DROP TABLE ' + @TEMP) 

In execution:

EXEC MF_DROP #A CREATE TABLE #A (I INT) .... 

A better visual and easy way, if you are using Visual Studio, just open from menu bar,

View -> SQL Server Object Explorer

it should open like shown here

Select and Right Click the Table you wish to delete, then delete. Such a screen should be displayed. Click Update Database to confirm.

This method is very safe as it gives you the feedback and will warn of any relations of the deleted table with other tables.