Recently, we worked on a requirement to write an SQL script for automation purposes for our production environment. We had to check if the table existed, so we explored multiple approaches. Here, we will discuss the best approaches that we implemented.
Approach-1 Using the OBJECT_ID()
I have an existing table named “Product.” Let’s use the below SQL query to check if it really exists.
IF (OBJECT_ID('Product') IS NOT NULL )
BEGIN
PRINT 'Yes, Table Exists'
END
ELSE
BEGIN
PRINT 'No, Table does not Exist'
END
After executing the above SQL script, since the Product table exists for me, I got the expected Output as “Yes, Table Exists.” You can check out the screenshot below.
Check out: Create table if not exists SQL Server

When I changed the table name from ‘Product’ to ‘Product1’, which doesn’t exist for me, and reran the query, I got the expected output as “No, Table does not exist,” as shown in the screenshot below.

Check out How To Find Column Name In Table SQL Server
Approach-2 Using the INFORMATION_SCHEMA.TABLES
We can use the INFORMATION_SCHEMA.TABLES statement.
Syntax
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Your table name')
BEGIN
print 'Yes !! Table exists';
END
Example
Let’s check if the Product table exists using the SQL query below.
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'product')
BEGIN
print 'Yes !! Table exists';
END
After executing the above query, we got the expected output, as shown in the screenshot below.

Check out How To Check If Column Exists In SQL Server Database
Approach-3 Using sys.tables
We can also use the sys.tables for this purpose.
Syntax
IF EXISTS (SELECT 1
FROM sys.tables
WHERE name = 'Your table name' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
print 'Yes !! Table exists';
END
Example
Let’s check if the Product table exists using the SQL query below.
IF EXISTS (SELECT 1
FROM sys.tables
WHERE name = 'product' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
print 'Yes !! Table exists';
END
After executing the above query, we got the expected output, as shown in the screenshot below.

Check out How To Change Column Name In SQL Server
Approach-4 Using sysobjects
We can also use the sysobjects for this purpose.
Syntax
IF EXISTS (SELECT 1
FROM sysobjects
WHERE name = 'TableName' AND xtype = 'U')
BEGIN
print 'Yes !! Table exists';
END
Example
Let’s check if the Ordes table exists using the SQL query below.
IF EXISTS (SELECT 1
FROM sysobjects
WHERE name = 'product' AND xtype = 'U')
BEGIN
print 'Yes !! Table exists';
END
After executing the above query, I got the expected output, as shown in the screenshot below.

Check out How To Check Table Description In SQL Server
Approach-5 For the temp table
Similarly, we can use the script below to check if the temp table exists.
Syntax
IF OBJECT_ID('tempdb..#TemptableName') IS NOT NULL
BEGIN
print 'Yes !! Table exists';
END
Example
IF OBJECT_ID('tempdb..#VehicleNew') IS NOT NULL
BEGIN
print 'Yes !! Table exists';
END
After executing the above query, I got the expected output, as shown in the screenshot below.

Conclusion
As mentioned in the article above, these approaches make checking if a table exists in an SQL server is easy.
You may also like following the articles below.

Grey is a highly experienced and certified database expert with over 15 years of hands-on experience in designing, implementing, and managing complex database systems. Currently employed at WEX, USA, Grey has established a reputation as a go-to resource for all things related to database management, particularly in Microsoft SQL Server and Oracle environments. He is a Certified Microsoft SQL Server Professional (MCSE: Data Management and Analytics) and Oracle Certified Professional (OCP), with Extensive database performance tuning and optimization knowledge and a proven track record in designing and implementing high-availability database solutions.