How to check if a Table exists in SQL Server

In this article we will show you, How to check if a Table exists in SQL Server or not with example. This is one of the SQL Server Frequently Asked Question.

TIP: Before you start creating A TABLE, It is always advisable to check if a Table exists, or not.

Approach 1: Check if a Table exists in SQL Server or Not

In this example we will show you, How to check whether a table exists in SQL Server or not using the OBJECT_ID. Here we used the IF ELSE statement to print different output (Message) based on the condition.

SQL CODE

-- Query:- SQL check if table exists before creating
USE [SQLTEST]
GO
IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL 
BEGIN
  PRINT 'Table Exists in SQL Test Database'
END
ELSE
BEGIN
PRINT 'Table Does not Exists'
END

OUTPUT

Approach 2: Check if a Table exists in SQL Server or Not

In this example we will show you, How to check whether a table exists in SQL Server or not using the Information_schema.tables. Here we used the SQL EXISTS Operator to check whether the table Employees present in the database or not. And if it is true, then it will return the first PRINT statement otherwise, it returns the PRINT statement inside the ELSE block

SQL CODE

-- Query:- SQL check if table exists before creating
USE [SQLTEST]
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_NAME = N'Employees')
BEGIN
  PRINT 'Table Exists in SQL Test Database'
END
ELSE
BEGIN
PRINT 'Table Does not Exists'
END

OUTPUT

Let me show you, what are the list of columns present in the INFORMATION_SCHEMA.TABLES

SQL CODE

USE [SQLTEST]
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES

OUTPUT

Now, let me try with the table that don’t exists.

Approach 3: Check if a Table exists in SQL Server or Not

In this example we will show you, How to check whether a table exists in SQL Server or not using the sys.Objects.

SQL CODE

-- Query:- SQL check if table exists before creating
USE [SQLTEST]
GO
IF EXISTS(SELECT 1 FROM sys.Objects 
    WHERE  Object_id = OBJECT_ID(N'dbo.Employees') 
           AND Type = N'U')
BEGIN
  PRINT 'Table Exists in SQL Test Database'
END
ELSE
BEGIN
PRINT 'Table Does not Exists'
END

OUTPUT

Let me show you the list of columns present in the sys.Objects. Here type = U means User tables

SQL CODE

-- Query:- SQL check if table exists before creating
USE [SQLTEST]
GO
SELECT * FROM sys.Objects
WHERE TYPE = N'U'

OUTPUT

Approach 4: Check if a Table exists in SQL Server or Not

In this example we will show you, How to check whether a table exists in SQL Server or not using the sys.Tables.

SQL CODE

-- Query:- SQL check if table exists before creating
USE [SQLTEST]
GO
IF EXISTS(SELECT 1 FROM sys.Tables 
          WHERE  Name = N'Employees')
BEGIN
  PRINT 'Table Exists in SQL Test Database'
END
ELSE
BEGIN
PRINT 'Table Does not Exists'
END

OUTPUT

Let me show you the list of columns present in the sys.Tables.

SQL CODE

-- Query:- SQL check if table exists before creating
USE [SQLTEST]
GO
USE [SQLTEST]
GO
SELECT * FROM sys.Tables

OUTPUT

Thank You for Visiting Our Blog