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.
My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.