How to drop a table if it exists?
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
Accepted Answer
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.#T', 'U') IS NOT NULL
DROP TABLE #T;
SQL Server 2016+ has a better way, using DROP TABLE IF EXISTS …. See the answer by @Jovan.
Popular Answer
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.
Read more… Read less…
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