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
|
1 2 3 4 5 6 7 8 9 10 11 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- 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
|
1 2 3 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- 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
|
1 2 3 4 5 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- 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
|
1 2 3 4 5 6 |
-- 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
Leave a Reply