Create Table If Not Exists SQL Server

by

Unlike MySQL or PostgreSQL, SQL Server doesn’t have a native “CREATE TABLE IF NOT EXISTS” statement, but I’ll show you four simple methods that I’ve used successfully to achieve this.

Create Table If Not Exists SQL Server

Method-1: Using OBJECT_ID()

The OBJECT_ID function provides the most reliable and widely-supported method for checking table existence across all SQL Server versions.

Syntax

IF (OBJECT_ID('Table name') IS NOT NULL )
BEGIN
  PRINT 'Table with the same name available'
END
ELSE
BEGIN
    CREATE TABLE TableName (
    Column1 datatype,
    Column2 datatype, 
    Column3 datatype
);
END

Example

We can execute the query below to create a brand new table SalesAz if doesn’t exist.

IF (OBJECT_ID('SalesAz') IS NOT NULL )
BEGIN
  PRINT 'Table with the same name available'
END
ELSE
BEGIN 
PRINT 'No Table available with this name. Let us create a new table'
  CREATE TABLE SalesAz (
    SalesID INT,
    ProductName nVARCHAR(50),
    ProductCategory nVARCHAR(50)
);
END

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

Create Table If Not Exists SQL Server

Now, let me rerun the same query. I received the expected output as “Table with the same name available.” Since the table already exists with the same name, please refer to the screenshot below for your reference.

create table if not exists sql server 2019

Method-2: Using the INFORMATION_SCHEMA.TABLES

Syntax

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

Example

Here, we will execute the query below to verify if a table named ‘SalesNew’ 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 = 'SalesNew')
BEGIN
    print 'Table with the same name exists';
END
ELSE
BEGIN
PRINT 'No, Table not available. Let us create the table'
    CREATE TABLE SalesNew (
    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.

How To Create Table If Not Exists SQL Server
Create table if not exists in SQL Server

Method-3 Using the sysobjects

Syntax

IF EXISTS (SELECT 1 
           FROM sysobjects 
           WHERE name = 'TableName' AND xtype = 'U')
BEGIN
    print 'Table with the same name exists';
END
ELSE
BEGIN
PRINT 'No, Table not available. 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 'Table with the same name exists';
END
ELSE
BEGIN
PRINT 'No, Table not available. Let us create the table'
    CREATE TABLE AzureUSA (
    ProductID INT,
    ProductName nVARCHAR(50),
    ProductBrand nVARCHAR(50)
);
print 'Table Created successfully';
END

Method-4: Using the sys.tables

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

Example

We can execute the query below to create the CustomerActivities table.

IF EXISTS (SELECT 1 
           FROM sys.tables 
           WHERE name = 'CustomerActivities' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    print 'Table with the same name exists';
END
ELSE
BEGIN
PRINT 'No, Table not available. Let us create the table'
    CREATE TABLE CustomerActivities (
    CustomerID INT,
    CusterName nVARCHAR(50),
    Desg nVARCHAR(50)
);
print 'Table Created successfully';
END

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

Create Table If Not Exists SQL
How to Create Table If Not Exists SQL

Conclusion

The “create table if not exists” pattern is essential for building reliable, maintainable SQL Server applications. The methods I’ve shared in this comprehensive guide—from the reliable OBJECT_ID function to advanced dynamic SQL implementations—represent well-tested approaches that I’ve successfully used.

You may also like the following articles: