How to check if a Table exists in SQL Server 3

How to check if a Table exists in SQL Server

by

How to check if a Table exists in SQL Server or not with an example?. It is one of the SQL Server Frequently Asked Question.

Check if a Table exists in SQL Server or Not approach 1

In this example, we 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 outputs (Message) based on the condition result.

-- Query:- Sql Server check 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

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

How to check if a Table exists in SQL Server 1

Approach 2: Check if a Table exists or Not

In this example, we are using the Information_schema.tables to check whether a table exists or not

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 statement inside the ELSE block

-- Query:- Sql Server check 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
How to check if a Table exists in SQL Server 2

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

USE [SQLTEST]
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES
How to check if a Table exists in SQL Server 3

Now, let me try with the table that doesn’t exist.

How to check if a Table exists in SQL Server 5

Approach 3: Check if a Table exists or Not

Here, we check whether a table exists in SQL Server or not using the sys.Objects.

-- 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
How to check if a Table exists in SQL Server 5

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

-- Query:- SQL check if table exists before creating
USE [SQLTEST]
GO
SELECT * FROM sys.Objects
WHERE TYPE = N'U'
How to check if a Table exists in SQL Server 6

Check if a Table exists or Not Approach 4

In this example, we will show how to check whether a table exists or not using the sys.Tables.

-- Query:- Sql Server check 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
How to check if a Table exists in SQL Server 7

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

-- Query:- SQL check table exists before creating
USE [SQLTEST]
GO
USE [SQLTEST]
GO
SELECT * FROM sys.Tables
How to check if a Table exists in SQL Server 8