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!

14 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
    select 0 as tableExists

  5. Nice post. I learn something totally new and challenging on blogs I stumbleupon every day.
    It will always be helpful to read through articles from other authors and use something from other web sites.

  6. I’ll immediately cluth your rss feed as I can not find your e-mail subscription link oor e-newsletterservice.
    Do you’ve any? Kindly let me recognize in ordfer that I may subscribe.


  7. Pingback: Recursive Common Table Expressions (CTEs) – SQL Server |

  8. Thanks on your marvelous posting! I seriously enjoyed reading it, you
    can be a great author.I will be sure to bookmark your blog and will eventually come back later on. I want to encourage you continue your great work, have a nice evening!

  9. Do you mind if I quote a few of your posts as
    long as I provide credit and sources back
    to your webpage? My blog site is in the very same area of interest as yours and my users would certainly benefit from some of the information you provide here.
    Please let me know if this alright with you. Thanks a lot!

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s