How to create table in MSSQL only if it does not exist

Often you wanna make sure some tables in database exist, but you want to avoid getting the error message “There is already an object named ‘yourtable’ in the database.”, that you would get by simply issuing the CREATE TABLE command.

For these cases, MSSQL offers keyword EXISTS (most SQL engines provide similar facilities; e.g. MySQL C API allows you to directly check for the existance of database and/or table).

All you have to do in MSSQL is make sure the table is not yet registered in the sysobjects table, using the following SQL command:

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[tablename]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[tablename] ( columns specification );

where “tablename” is name of table you want to create, and “columns specification” is the usual definition of table’s columns. Feel free to extend the table definition to your liking.

Or you might want to DROP the table before re-creating it:

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[tablename]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[tablename];
CREATE TABLE [dbo].[tablename] ( columns specification );

Yes, that’s all. Good luck!

9 responses to “How to create table in MSSQL only if it does not exist

  1. I’m getting the following error:

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘’’.

    I just copypasta’d it from this site and replaced the tablename thingy, but it doesn’t seem to work. Changed some of the quotes around, but still can’t get it to work. Any ideas?

    I’m amazed by the excessive complexity of just determining if a table exists in MSSQL, =D. ‘IF NOT EXISTS table_name’ > this.

  2. That’s a problem of wordpress, that it converts all normal quotes (single and double) to the fancy ones. You have to convert all of them to normal ones by hand, sorry.

  3. I use this approach, think it is easier. :-)

    IF OBJECT_ID(N’[dbo].[tablename]‘, N’U’) IS NULL
    CREATE TABLE [dbo].[tablename] ( columns specification );

  4. There’s a bug in the original code.

    You ask for NOT EXISTS which evaluates to true oif the SELECT returns FALSE, if the object IS NOT htere and then you additionally ask if that object is a user table or not.

    This is contradictory.

    IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N’[dbo].[ch_sbt_hm1212]‘) and xtype=N’U’)
    –do stuff here …
    select 1 as tableExists
    else
    select 0 as tableExists

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s