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.