How to Check if a Table Already Exists in SQL Server
To check if a table already exists in the SQL Server database, use these methods:
- Using the OBJECT_ID and the IF ELSE statement
- Using the sys.Objects
- Using the sys.Tables
- Using the INFORMATION_SCHEMA.TABLES and SQL EXISTS Operator
Using the OBJECT_ID and the IF ELSE statement to check whether a table exists or not
SQL OBJECT_ID function returns the database object identification number if the object exists. By pairing it with the IF ELSE statement, we can check if a table with the same name already exists in the SQL Server.
Syntax:
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
Using the INFORMATION_SCHEMA.TABLES and SQL EXISTS Operator to check whether a table exists or not
The INFORMATION_SCHEMA.TABLES is a system view that contains metadata about all tables in the database. Using EXISTS operator we can check if a table already exists in a database.
Syntax:
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
Using the sys.Objects and SQL EXISTS Operator to check whether a table exists in SQL Server or not
The sys.Objects is a system view that contains every user-defined object created within the database. By pairing it with the EXISTS operator, we can verify if the table already exists in the SQL Server database.
Syntax:
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.
Using the sys.Tables to check whether a table exists or not
sys.Tables is a system view, that contains each table in the current database. Using it, we can check if a table already exists in the SQL Server Database.
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.'
ENDthe
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 the SQL Server database or not. Checking for table existence before creation helps in avoiding duplication errors, ensures data integrity, and enables efficient database management.
There are multiple methods in SQL Server to check if a table already exists in a database. Here, we will discuss these methods and learn the .