Check whether a Table exists in SQL Server database or not

Before creating a table, it is always advisable to check whether the table exists in the SQL Server database or not. Checking for table existence before creation helps in avoiding duplication errors, ensures data integrity, and enables efficient database management.

There are multiple methods in SQL Server to check if a table already exists in a database. Here, we will discuss these methods and learn the .

How to Check if a Table Already Exists in SQL Server

To check if a table already exists in the SQL Server database, use these methods:

• Using the OBJECT_ID and the IF ELSE statement
• Using the sys.Objects
• Using the sys.Tables
• Using the INFORMATION_SCHEMA.TABLES and SQL EXISTS Operator

Using the OBJECT_ID and the IF ELSE statement to check whether a table exists or not

SQL OBJECT_ID function returns the database object identification number if the object exists. By pairing it with the IF ELSE statement, we can check if a table with the same name already exists in the SQL Server.

Syntax:

USE [DB_NAME]
GO
IF OBJECT_ID('table_name', 'U') IS NOT NULL
BEGIN
PRINT 'Table exists.'
END
ELSE
BEGIN
PRINT 'Table does not exist.'
END

Using the INFORMATION_SCHEMA.TABLES and SQL EXISTS Operator to check whether a table exists or not

The INFORMATION_SCHEMA.TABLES is a system view that contains metadata about all tables in the database. Using EXISTS operator we can check if a table already exists in a database.

Syntax:

USE [DB_NAME]
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N'table_name')
BEGIN
PRINT 'Table exists.'
END
ELSE
BEGIN
PRINT 'Table does not exist.'
END

Using the sys.Objects and SQL EXISTS Operator to check whether a table exists in SQL Server or not

The sys.Objects is a system view that contains every user-defined object created within the database. By pairing it with the EXISTS operator, we can verify if the table already exists in the SQL Server database.

Syntax:

USE [DB_NAME]
GO
IF EXISTS(SELECT 1 FROM sys.Objects
WHERE Object_id = OBJECT_ID(N'table_name')
AND Type = N'U')
BEGIN
PRINT 'Table exists.'
END
ELSE
BEGIN
PRINT 'Table does not exist.'
END

Output :

Table does not exists.

Using the sys.Tables to check whether a table exists or not

sys.Tables is a system view, that contains each table in the current database. Using it, we can check if a table already exists in the SQL Server Database.

Query :

USE [DB_NAME]
GO
IF EXISTS(SELECT 1 FROM sys.Tables
WHERE Name = N'table_name')
BEGIN
PRINT 'Table exists.'
END
ELSE
BEGIN
PRINT 'Table does not exist.'
ENDthe 

Check whether a Table exists in SQL Server database Example

Let us assume we have a database name "SQL_DBA" and we need to create a new table "geek_demo" -

Query:

USE [SQL_DBA]
GO
IF OBJECT_ID('geek_demo', 'U') IS NOT NULL
BEGIN
PRINT 'Table exists.'
END
ELSE
BEGIN
PRINT 'Table does not exist.'
END

Output:

Table does not exists.

Let us create the table.

Query

CREATE TABLE geek_demo (id int, name varchar(200));

Now, let us check whether the table is created or not -

Query

USE [DB_NAME]
GO
IF EXISTS(SELECT 1 FROM sys.Objects
WHERE Object_id = OBJECT_ID(N'table_name')
AND Type = N'U')
BEGIN
PRINT 'Table exists.'
END
ELSE
BEGIN
PRINT 'Table does not exist.'
END

Output

Table exists.