How To Check If Table Exists In SQL Server

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

how to check if a table exists in 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.

how to check if the table exists in sql server

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.

how to check if table exists in sql server

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 if the table exists in sql server

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 if a table exists in sql server

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.

query to check if table exists in sql server

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.