IF EXISTS DROP TABLE in SQL Server

Managing the existence of tables, databases, and columns in SQL Server is a critical aspect of database administration. In this blog post, we’ll explore various way we can check if a tables exists before dropping it. As a SQL programmer know these different techniques goes long way becasue every a technical challenge can come in many forms and we should be prepared with how to better handle it.

Checking and Dropping a Table

Using SQL Server 2016 or Above

Starting with SQL Server 2016, you can use a more concise syntax to drop a table if it exists:

-- Check and drop table if it exists (SQL Server 2016 or above)
DROP TABLE IF EXISTS dbo.TableName

This syntax simplifies the process, eliminating the need for a separate existence check.

Using OBJECT_ID Function

For versions prior to SQL Server 2016, you can use the OBJECT_ID function to check if a table exists and drop it:

-- Check and drop table if it exists (compatible with SQL Server 2014 and below)
IF OBJECT_ID('dbo.TableName', 'U') IS NOT NULL
DROP TABLE dbo.TableName

This method ensures compatibility with older versions of SQL Server.

Using sysobjects

The traditional approach using sysobjects is still valid:

-- Check and drop table if it exists (compatibility across SQL Server versions)
IF EXISTS (
  SELECT NAME
  FROM anydbnameOptional.dbo.sysobjects
  WHERE type = 'u'
   AND NAME = 'TableName'
)
DROP TABLE TableName

This snippet checks for the existence of the table ‘TableName’ in the specified database and drops it if found.

Checking Database Existence

Checking for the existence of a database remains unchanged:

-- Check if a database exists
IF EXISTS (
  SELECT NAME
  FROM master..sysdatabases
  WHERE NAME = 'DatabaseName'
)
SELECT 'ok'

This code snippet ensures that the specified database, ‘DatabaseName,’ exists.

Declaring and Printing Database Name

Retrieving and printing the current database name is done using the same method:

DECLARE @anotherWay VARCHAR(100)

SET @anotherWay = DB_NAME()

PRINT @anotherWay

This snippet assigns the current database name to the variable @anotherWay and prints it.

Checking and Handling Column Existence

Using INFORMATION_SCHEMA.COLUMNS

Checking for column existence with INFORMATION_SCHEMA.COLUMNS:

IF EXISTS (
  SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'myTableName'
   AND COLUMN_NAME = 'myColumnName'
)
-- Column Exists - Perform necessary actions here

Using Deprecated sys.columns

For legacy purposes, the deprecated sys.columns approach is included:

IF EXISTS (
  SELECT *
  FROM sys.columns
  WHERE NAME = N'columnName'
   AND Object_ID = Object_ID(N'tableName')
)
BEGIN
  -- Column Exists - Perform necessary actions here
END

It is recommended to use INFORMATION_SCHEMA.COLUMNS for better compatibility.

By incorporating these refined code snippets into your SQL Server database management practices, you ensure a modern and efficient approach to handling tables, databases, and columns.