Cry How to...

Create a table unless it already exists


To create a table in SQL Server, unless it has already been created:

if not exists (select * from sysobjects
  where name='<table_name>' and xtype='U')
    create table <table_name>' ...

where <table_name> is the name of the table. The 'xtype' field holds the type of object, 'U' denoting a user defined table.

For example:

if not exists (select * from sysobjects where name='cars' and xtype='U')
    create table cars (
        Name varchar(64) not null
    )
go


These notes have been tested against SQL Server 7 and SQL Server 2000, SQL Server 2005 and SQL Server 2008.



About the author: is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.