How to drop table if exists in sql server

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

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

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.

Another way 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];