How to check if a table exist in Sql Server

As data analyst, one common thing to do in our scripts, specially for creating a table is to check if that table already exist.


There are some methods to check if a table exist in Sql Server.

Demo Table

We are going to create a table and a temporary table for testing purposes.

--CREATING TABLE
CREATE TABLE TEST_TABLE(
    TESTVAR VARCHAR(50)
);
--CREATING TEMP TABLE
SELECT * 
INTO ##TMP_TEST_TABLE
FROM TEST_TABLE

This is going to create a table and by using that table it is going to create a temp table.

Method 1: INFORMATION_SCHEMA.TABLES

Information.Schema.tables view allows you to get information of the tables and views that you have in your database.

After creating the tables we are going to check if those tables exist by using INFORMATION_SCHEMA.TABLES.

SELECT *
FROM INFORMATION_SCHEMA.TABLES

As you can see we are able to see TEST_TABLE in table list but there is no temp tables available in this view as they are not stored in local database, they are store in tempdb.

We are going to use INFORMATION_SCHEMA.TABLES for checking if TEST_TABLE already exist, if it doesn’t we are going to create it.

By doing this we are avoiding an error related with the existence of the table.

IF(EXISTS 
  (SELECT * 
   FROM INFORMATION_SCHEMA.TABLES      
   WHERE TABLE_SCHEMA LIKE 'dbo'            
   AND TABLE_NAME = 'TEST_TABLE')) 
BEGIN DROP TABLE TEST_TABLE;    
END 
--CREATING TABLE 
CREATE TABLE TEST_TABLE(     
   TESTVAR VARCHAR(50) 
);

Method 2: OBJECT_ID() function

This function returns the database object identification number of a schema object. We are going to use this function for checking the existence of a table.

 
SELECT OBJECT_ID('test.dbo.TEST_TABLE','U') AS OBJECT_ID_TABLE;

Syntax consist in invoking inside the function the ([Database Name].[Schema].[Table Name],[Object Type]).

We are going to use ‘U’ as [Object Type] as U equals Table (user-defined), In case you don’t want to use ‘U’, it would not affect the results as it is going to look into all available objects.

If we want to check the existence of a temp table, we have to check in tempdb database as that database store temporary tables.

 
SELECT OBJECT_ID('tempdb..##TMP_TEST_TABLE','U') AS OBJECT_ID_TEMP;

If object exist, OBJECT_ID function is going to return a number, if not it is going to return a NULL value.

We can check if a table exist and create that table if it does not exist by using OBJECT_ID in the following way.

 
--QUERY FOR DROPPING AN EXISTING TABLE
IF(EXISTS (
    SELECT OBJECT_ID('test.dbo.TEST_TABLE','U')))
BEGIN
DROP TABLE TEST_TABLE;   
END
--CREATING TABLE
CREATE TABLE TEST_TABLE(
    TESTVAR VARCHAR(50)
);

The following query check if a temp table exist and create that table if it does not exist.

  
--QUERY FOR DROPPING AN EXISTING TEMP TABLE 
IF OBJECT_ID('tempdb.dbo.##TMP_TEST_TABLE ','U') IS NOT NULL
	DROP TABLE ##TMP_TEST_TABLE ;
--CREATING TEMP TABLE  
SELECT *  
INTO ##TMP_TEST_TABLE 
FROM TEST_TABLE 

Method 3: SYS.TABLES

Sys.tables is a table that contains the names of the tables that exist in our database. When we create a table in our database a record is created in the sys.tables.

The following query gives you table’s information by using sys.tables.

 
--CHECKING IF TEST_TABLE exist
SELECT * 
FROM SYS.TABLES
WHERE name LIKE 'TEST_TABLE'

If table does not exist it is going to return null values, otherwise it is going to return a row that contains the information of the table

If you want to check the existence of a temp table you will have to check if that table exist in temp database by applying the following query

 
--CHECKING IF TEST_TABLE exist
SELECT *
FROM TEMPDB.SYS.TABLES
WHERE name LIKE '##TMP_TEST_TABLE'

We can check if a table exist and create that table if it does not exist by using OBJECT_ID in the following way.

--QUERY FOR DROPPING AN EXISTING TABLE
IF(EXISTS (
    SELECT * 
    FROM SYS.TABLES
    WHERE name LIKE 'TEST_TABLE'
))
BEGIN
DROP TABLE TEST_TABLE;   
END
--CREATING TABLE 
CREATE TABLE TEST_TABLE(     
    TESTVAR VARCHAR(50) 
);

The following query check if a temp table exist and create that table if it does not exist.

--QUERY FOR DROPPING AN EXISTING TEMP TABLE
IF(EXISTS (
    SELECT *
    FROM TEMPDB.SYS.TABLES
    WHERE name LIKE '##TMP_TEST_TABLE'
))
BEGIN
DROP TABLE ##TMP_TEST_TABLE;   
END
--CREATING TEMP TABLE 
SELECT * 
INTO ##TMP_TEST_TABLE
FROM TEST_TABLE

Method 4: SYS.SYSOBJECTS view

This table contains the information of each object that is created in the database, it has one record for each of them.

This table belongs to sql server 2000 and it will be removed in the future, so try to not use it.

This function is similar than OBJECT_ID() function,

The following query gives you table’s information by using sys.objects.

SELECT * 
FROM sys.sysobjects
WHERE TYPE = 'U'
AND name LIKE 'TEST_TABLE'

If you want to check temp table information execute the following query.

SELECT * 
FROM tempdb.sys.sysobjects
WHERE TYPE = 'U'
AND name LIKE '##TMP_TEST_TABLE'

By concatenating the previous queries, we can check the existence of a table an execute some code in case it does not exist.

--QUERY FOR DROPPING AN EXISTING TABLE
IF(EXISTS (
    SELECT * 
    FROM sys.sysobjects
    WHERE TYPE = 'U'
    AND name LIKE 'TEST_TABLE'
))
BEGIN
DROP TABLE TEST_TABLE;   
END
--CREATING TABLE 
CREATE TABLE TEST_TABLE(     
    TESTVAR VARCHAR(50) 
);

Also we can check if a temp table does or not exist by executing the following query.

--QUERY FOR DROPPING AN EXISTING TABLE
IF(EXISTS (
    SELECT * 
    FROM tempdb.sys.sysobjects
    WHERE TYPE = 'U'
    AND name LIKE '##TMP_TEST_TABLE'
))
BEGIN
DROP TABLE TEST_TABLE;   
END
--CREATING TEMP TABLE 
SELECT * 
INTO ##TMP_TEST_TABLE
FROM TEST_TABLE

Conclusions

All methods works on a similar way, but object_id function seems to be more easier to use, this method also includes a way to verify the existence of a temporary table.


Leave a Reply

Your email address will not be published. Required fields are marked *