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 SQL Server database or not.

Alternative 1 :
Using the OBJECT_ID and the IF ELSE statement to check whether a table exists or not.

Query :

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

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

Query :

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

Alternative 3 :
Using the sys.Objects to check whether a table exists in SQL Server 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 does not exists.

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

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

Example :
Let us assume we have database name “SQL_DBA” and we need to create a new table “geek_demo” –

Input-1 :

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-1 :

Table does not exists.

Let us create the table.

Create table geek_demo (id int, name varchar(200));

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

Input-2 :

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-2 :

Table exists.