How to drop table if exists in sql server
By FoxLearn 5/31/2024 8:43:42 AM 606
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];
- How to Download and Restore Northwind database to SQL Server
- How to set time to 00:00:00 with GETDATE() in SQL
- Restoring MySQL Databases with mysqldump
- How to use Oracle linked server shows all tables
- Download backup of Northwind database for SQL Server
- How to Convert varchar to uniqueidentifier in SQL Server
- How to fix 'The transaction log for the database is full due to ACTIVE_TRANSACTION'
- How to use ROW_NUMBER Function in SQL Server