How to check if a Table exists in SQL Server or not with example?. It is one of the SQL Server Frequently Asked Question.
TIP: Before you start creating a TABLE, It is always advisable to check if a SQL Server Table exists or not.
Approach 1: Check if a Table exists in SQL Server or Not
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.
-- 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
OUTPUT
Approach 2: Check if a Table exists or Not
In this frequent 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
OUTPUT
Let me show you, what are the list of columns present in the INFORMATION_SCHEMA.TABLES
USE [SQLTEST] GO SELECT * FROM INFORMATION_SCHEMA.TABLES
OUTPUT
Now, let me try with the table that doesn’t exist.
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
OUTPUT
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'
OUTPUT
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
OUTPUT
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
OUTPUT