How to drop table if exists in sql server

To drop a table in SQL Server only if it exists, you can use the following SQL statement:

IF OBJECT_ID('YourTableName', 'U') IS NOT NULL
    DROP TABLE YourTableName;

- OBJECT_ID('YourTableName', 'U'): This function retrieves the object identification number (ID) of a specified object in the current database. In this case, it checks if the table with the name 'YourTableName' exists. The second argument 'U' specifies that it's a user-defined table.

- IS NOT NULL: Checks if the object ID returned by OBJECT_ID is not null, which means the table exists.

- DROP TABLE YourTableName;: This statement drops the table named 'YourTableName' if it exists.

Replace 'YourTableName' with the name of the table you want to drop. This statement first checks if the table exists in the database using OBJECT_ID(), and if it does, it drops the table using DROP TABLE.

By wrapping the DROP TABLE statement inside the IF block, you ensure that the table is dropped only if it exists in the database. This helps prevent errors that might occur if you attempt to drop a table that doesn't exist.

Alternative you can use:

1. DROP TABLE if exists querying the sys.tables

IF EXISTS(SELECT * FROM sys.tables WHERE SCHEMA_NAME(schema_id) LIKE 'dbo' AND name like 'YourTableName')  
   DROP TABLE [dbo].[YourTableName];  
GO

2. DROP TABLE if exists querying the INFORMATION_SCHEMA.TABLES

-- check if table exists in INFORMATION_SCHEMA.TABLES we will drop it
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'YourTableName' AND TABLE_SCHEMA = 'dbo')
   DROP TABLE [dbo].[YourTableName];
GO

3. SQL Server 2016 and up, you can use DROP TABLE IF EXISTS

-- DROP TABLE only if it exists 
DROP TABLE IF EXISTS [dbo].[YourTable];