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')

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.

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.