Cry How to...

Create a view unless it already exists

To create a view in SQL Server, unless it has already been created. Conceptually this is simply a case of checking whether the view exists and if it doesn't then creating it. Unfortunately, it is a little more complicated that this because SQL server insists that "create view" statement must be the first statement in a query batch. To get around this the creation of the view is executed within a separate Transact-SQL batch:

if not exists (select * from sysobjects
  where name='view-name' and xtype='V')
    exec ('create view view-name as  ...')

where view-name is the name of the table. The 'xtype' field holds the type of object, 'V' denoting a view.

For example:

if not exists (select * from sysobjects where name='cars' and xtype='V')
    exec ('create view cars as select * from vans where type=1')
go

An alternative approach would be to drop the view and recreate it, but this may cause problems in some circumstances.

These notes have been tested against SQL Server 2000.