Create table if not exists SQL Server

SQL Server does not directly approach the “CREATE TABLE IF NOT EXISTS” syntax like MySQL. Recently, I was required to check if one of the tables in the SQL server database exists. If it doesn’t, I must create a new table with the same name. After analysis, I have identified 4 best approaches to achieve this.

Approach-1: Using the OBJECT_ID()

We can use the OBJECT_ID() for this purpose.

Syntax

IF (OBJECT_ID('Table name') IS NOT NULL )
BEGIN
  PRINT 'Yes, Table Exists'
END
ELSE
BEGIN
    CREATE TABLE TableName (
    Column1 datatype,
    Column2 datatype, 
    Column3 datatype
);
END

Example

I have executed the below query to create a table ProductNew.

IF (OBJECT_ID('ProductNew') IS NOT NULL )
BEGIN
  PRINT 'Yes, Table Exists'
END
ELSE
BEGIN 
PRINT 'No, Table does not Exists. Let us create the table'
  CREATE TABLE ProductNew (
    ProductID INT,
    ProductName nVARCHAR(50),
    ProductBrand nVARCHAR(50)
);
END

After executing the above query, I got the expected output, and the ProductNew table was created successfully.

Check out How To Check If Table Exists In SQL Server

Let us cross-check with the select statement to see if the table has been created. The screenshot below shows that the table was created successfully since it was unavailable in my database.

When I reran the same query, I got the expected output as “Yes, Table Exists.” Since the table has already been created with the same name as shown in the screenshot below,

Approach-2: Using the INFORMATION_SCHEMA.TABLES

We can also use the INFORMATION_SCHEMA.TABLES for this purpose.

Syntax

IF EXISTS (SELECT 1 
           FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_SCHEMA = 'dbo' 
           AND TABLE_NAME = 'TableName')
BEGIN
    print 'Yes !! Table exists';
END
ELSE
BEGIN
    CREATE TABLE TableName (
    Column1 datatype,
    Column2 datatype, 
    Column3 datatype
);
END

Check out How To Find Column Name In Table SQL Server

Example

Here, we will execute the below query to check if the table with the name ProductLatest exists. If not, it will create a new table with the same name.

IF EXISTS (SELECT 1 
           FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_SCHEMA = 'dbo' 
           AND TABLE_NAME = 'ProductLatest')
BEGIN
    print 'Yes !! Table exists';
END
ELSE
BEGIN
PRINT 'No, Table does not Exists. Let us create the table'
    CREATE TABLE ProductNew (
    ProductID INT,
    ProductName nVARCHAR(50),
    ProductBrand nVARCHAR(50)
);
print 'Table Created successfully';
END

After executing the above query, I got the expected output, as shown below.

Check out How To Create A Table Valued Function In SQL Server

Approach-3: Using the sys.tables

The sys.tables can be used for this purpose as well.

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
ELSE
BEGIN
PRINT 'No, Table does not Exists. Let us create the table'
    CREATE TABLE TableName (
    column1 datatype,
    column2 datatype,
    column3 datatype
);
print 'Table Created successfully';
END

Example

I executed the below query, which created the AzureProduct table since it did not exist. The screenshot below is for your reference.

IF EXISTS (SELECT 1 
           FROM sys.tables 
           WHERE name = 'AzureProduct' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    print 'Yes !! Table exists';
END
ELSE
BEGIN
PRINT 'No, Table does not Exists. Let us create the table'
    CREATE TABLE AzureProduct (
    ProductID INT,
    ProductName nVARCHAR(50),
    ProductBrand nVARCHAR(50)
);
print 'Table Created successfully';
END

Check out How To Check If Column Exists In SQL Server Database

Approach-4 Using the 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
ELSE
BEGIN
PRINT 'No, Table does not Exists. Let us create the table'
    CREATE TABLE TableName (
    column1 datatype,
    column2 datatype,
    column3 datatype
);
print 'Table Created successfully';
END

Example

I executed the query below, which created the AzureUSA table since it did not exist. The screenshot below is for your reference.

IF EXISTS (SELECT 1 
           FROM sysobjects 
           WHERE name = 'AzureUSA' AND xtype = 'U')
BEGIN
    print 'Yes !! Table exists';
END
ELSE
BEGIN
PRINT 'No, Table does not Exists. Let us create the table'
    CREATE TABLE AzureUSA (
    ProductID INT,
    ProductName nVARCHAR(50),
    ProductBrand nVARCHAR(50)
);
print 'Table Created successfully';
END

Conclusion

You can easily use the IF (OBJECT_ID(‘table name) IS NOT NULL ), INFORMATION_SCHEMA.TABLES, sys.tables, sysobjects statement to check if the table exists in your SQL database, and if it is not available, you can use the standard create statement within the SQL script as discussed in this article to create your brand new table.

You may also like following the articles below.

• How to create a table with an identity column in SQL Server
• How to copy one table to another in SQL Server