Learning never exhausts the mind
Home >  Coding > SQL > SQL Server Create Table IF it Does NOT Exist

First Published 1st May 2011, Last Updated by

There are many reasons to create a table using a script, and a quick check if the table already exists will eliminate problematic errors, however, Microsoft SQL Server does not provide a complete user-friendly way for testing table existence.

MySql contains a very useful table construct element which checks that the table does not exist prior to creating it. MySql's create table if not exist construct will do as it says, it will create the table if it does not exist.

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

Microsoft SQL Server lacks the function of create table if not exist, meaning table creation queries will fail if the table already exists. You could drop the table before creating it, but again, you may run into problems if the table does not exist.

This function can be used to test if the table exists and, if it does not exist, create it.

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='tbl_name' and xtype='U')
    CREATE TABLE tbl_name (
        Name varchar(64) not null
    )
GO

It is nowhere near as elegant as the MySql version, but it functions in the same way.

4 thoughts on “SQL Server Create Table IF it Does NOT Exist
  • 21st February 2020 at 9:24 pm

    This would be a bit more elegant:
    IF OBJECT_ID('dbo.tbl_name', 'U') IS NULL CREATE TABLE dbo.tbl_name (VARCHAR(64));

    Reply
    • 28th February 2020 at 8:04 pm

      Thanks for the tip Greg. Worked great for me!

      Reply
    • 12th April 2020 at 3:16 pm

      what is dbo and U

      Reply
  • Jack
    6th December 2019 at 5:03 pm

    Thank you very much Tim!
    You saved me some time figuring it out.
    I would like to know why did they chose not support a function for it.

    Reply

Leave a Reply

Fields marked with * are mandatory.

We respect your privacy, and will not make your email public. Hashed email address may be checked against Gravatar service to retrieve avatars. This site uses Akismet to reduce spam. Learn how your comment data is processed.