Check if table exists then delete it in SQL Server


I am checking this article, which allow you to drop all tables of SQL Server database, but I just want to delete a specific table of the database, so how can I do it?

to check if table exists then drop it completely from the database, without risking any other DB table in SQL Server.


Asked by:- pika
1
: 4435 At:- 8/13/2021 12:41:50 PM
SQL drop if table exists SQL Server







2 Answers
profileImage Answered by:- vikas_jk

If you are using SQL Server older than 2016, you can use below query to check if table exists and then drop it

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

The above will drop table if it contains Rows and table exists, otherwise it will throw error.

OR

IF OBJECT_ID(N'dbo.TableName', N'U') IS NOT NULL  
   DROP TABLE [dbo].[TableName];  
GO

For Newer version (SQL Server 16+, Version 13.X), you can use below query (Works in SQL Azure database also)

DROP TABLE IF EXISTS dbo.YourTableName

Hope it helps.

2
At:- 8/13/2021 2:25:56 PM Updated at:- 12/13/2022 6:52:27 AM
Excellent thanks. 0
By : pika - at :- 8/16/2021 11:15:33 AM


profileImage Answered by:- neena

There are multiples ways to check if table exists in database using SQL

Using ObjectId we can use below SQL query

IF OBJECT_ID(N'dbo.TableName', N'U') IS NOT NULL
BEGIN
    PRINT 'Table Exists'
END

Using sys.Tables 

 IF EXISTS(SELECT 1 FROM sys.Tables WHERE  Name = N'TableName' AND Type = N'U')
 BEGIN
      PRINT 'Table Exists'
 END

Using INFORMATION_SCHEMA.TABLES view

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'TableName')
BEGIN
    PRINT 'Table Exists'
END

But if you want to check if table exits and delete it, then simplest would be to use

drop table if exists TableName

But this query only works starting from SQL Server 2016

1
At:- 2/23/2022 10:29:31 AM
good thanks 0
By : pika - at :- 3/6/2022 5:57:09 PM






Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use