How to drop a table if it exists?

Question

The table name is Scores.

Is it correct to do the following?

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

Ro

Answer 1

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.

Ovenware

Fwiw -- The 'U' for the second param apparently means "Only look for objects with this name that are tables". One source. So OBJECT_ID('TableName') isn't wrong, but it's not insanely precise either, thus 'U' in @Martin's excellent answer. – Lawford

Regarding the second param; here is another source , I used 'V' for a View. – Stallfeed

HI can you explain me what this second parameter means in OBJECT_ID('tempdb.dbo.#T', 'U'), for example this 'U' ? – Fluoroscope

@ZvonimirTokic it means "User defined Table". "IT" would be an internal, system defined, table. A full list is here msdn.microsoft.com/en-us/library/ms190324.aspx – Ovenware

Argh, my "one source" disappeared. Archived here & thanks, @TheRedPea for the second, more canonical, source. – Lawford

Answer 2

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.

Newton

This should be the accepted answer. Thanks for updating to current tech, @Jovan. – Cognizable

I rushed right out to use this when I found out it existed, but it still seems to wreak havoc on query plans, still true as of SQL Server 2019. When I switched from IF OBJECT_ID() IS NOT NULL DROP TABLE #T_Foo to DROP TABLE IF EXISTS #T_Foo, otherwise identical stored procedures changed from 10 msec to 25 seconds! So make sure you measure performance if you use this new syntax. – Tenishatenn

DROP IF EXISTS is super buggy. Recommend avoiding. – Kimon

Answer 3

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

Karlin

Is the if exists ansi compliant? – Ovenware

Be careful if you have more than one schema in the database. You might need to be specific about which [Scores] you are detecting and deleting. E.g. WHERE TABLE_NAME = 'Scores' AND TABLE_SCHEMA = 'dbo' – Tannenberg

@kiquenet Generally yes, but not when using the if exists - since this stops as soon as it returns one row. But I personally always do select 1 anyway. – Whiteley

Answer 4

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"

Askins

Thanks, changing dbo to tempdb made this work. I would also like to suggest adding the 'u' as mentioned in the accepted answer's comments. Thus, the full IF statement would look like this: IF OBJECT_ID('tempdb..#temp', 'U') – Colobus

Answer 5

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

Ashil

Answer 6

Or:

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

Keys

You can use sys.tables since 2005 to simplify this: if exists (select * from sys.tables where name = 'Scores') drop table Scores – Tetter

Answer 7

I hope this helps:

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

Conclusion

Answer 8

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]

Hanger

What about same name but different schema? The best way is here: https://mcmap.net/q/53529/-how-to-drop-a-table-if-it-exists – Curriculum

Answer 9

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.

Officialism

Answer 10

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;

Jilolo

This adds nothing new to the existing answers – Ovenware

Answer 11

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

Radmilla

Answer 12

I use:

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

Pueblo

Answer 13

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;

Picaresque

This is not valid SQL Server syntax – Ovenware

And I believe (Microsoft) SQL Server is not the only SQL database out there. FYI, Vertica, Oracle, Snowflake, and PostgreSQL; all support CASCADE option. So, my answer complements yours and you didn't have to downvote me because of that. – Picaresque

The question is tagged SQL Server – Ovenware

Answer 14

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

Caddish

Answer 15

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.

Bamberger

This question is related to SQL, not related to Visual Studio. Therefore, this answer is irrelevant to this question. – Anyhow

Btw, the question is "How to drop a table if it exists?" with a tag "sql-server" without specifically lining it to SQL only, therefore this answer is not only technically correct but some (knowing only basics) might find this more convenient and helpful instead. – Fortin