How to check if a view exists and create if it does not

Question

I want to create a view if not exists in SQL Server 2016

IF EXISTS(SELECT 1 FROM sys.views 
     WHERE Name = 'VI_ALL_CITIES_AS_CATEGORY')
BEGIN
CREATE VIEW VI_ALL_CITIES_AS_CATEGORY AS
    SELECT PERSONS.FIRST_NAME AS 'Име', PERSONS.LAST_NAME AS 'Фамилия', CITIES.CITY_NAME AS 'Град'
    FROM CITIES
    LEFT JOIN PERSONS ON CITIES.ID = PERSONS.CITY_ID ;
END

But it gives me error:

Incorrect syntax: 'CREATE VIEW' must be the only statement in the batch.

Unless I'm reading your SQL wrong, your `EXISTS` is checking for the existence, and then you're trying to `CREATE` it. If the view already exists, the `CREATE` will fail. — Thom A, Mar 07 '18 at 09:26
Another solution: `set noexec`: https://codereview.stackexchange.com/a/13858/40484 — JohnLBevan, Mar 07 '18 at 09:29

score 12 · Answer 1 · answered Mar 07 '18 at 09:37

You have 2 options:

1) If you are using SSMS or any client that can split your script into different batches:

IF EXISTS(SELECT 'view exists' FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'YourViewName'AND TABLE_SCHEMA = 'YourViewSchema')
BEGIN
    DROP VIEW YourViewSchema.YourViewName
END

GO -- This will make the next statement the first in it's batch

CREATE VIEW YourViewSchema.YourViewName AS
SELECT something = 1
FROM YourTable

GO

2) If you can't split code into batches, you will have to "fool" the engine using dynamic SQL to create your view:

 IF NOT EXISTS(SELECT 'view exists' FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'YourViewName'AND TABLE_SCHEMA = 'YourViewSchema')
    BEGIN
        DECLARE @v_ViewCreateStatement VARCHAR(MAX) = '
            CREATE VIEW YourViewSchema.YourViewName AS
                SELECT something = 1
                FROM YourTable'

        EXEC (@v_ViewCreateStatement)

    END

Note that 1) is an IF EXISTS and 2) is an IF NOT EXISTS.

The reason for this is that most DDL statements need to be first in a batch, so you can't put CREATE object statements after other statements, unfortunately.

score 6 · Accepted Answer · answered Mar 07 '18 at 09:26

6

Try this approach

IF NOT EXISTS
(
    SELECT 1
    FROM sys.views
    WHERE Name = 'VI_ALL_CITIES_AS_CATEGORY'
)
BEGIN

    EXEC('CREATE VIEW VI_ALL_CITIES_AS_CATEGORY AS SELECT 1 as Val')

END
GO

ALTER VIEW VI_ALL_CITIES_AS_CATEGORY
AS
SELECT 
    PERSONS.FIRST_NAME AS 'Име',
    PERSONS.LAST_NAME AS 'Фамилия',
    CITIES.CITY_NAME AS 'Град'
    FROM CITIES
       LEFT JOIN PERSONS 
          ON CITIES.ID = PERSONS.CITY_ID

answered Mar 07 '18 at 09:26

Jayasurya Satheesh

7,826
3
22
39

2

There's a GO statement in the middle, keep in mind that this won't work if this code is inside a stored procedure, for example. – EzLo Mar 07 '18 at 10:00

score 0 · Answer 3 · answered Dec 10 '21 at 21:39

If you don't wanna to delete your current view, you can create a new one with a temp name, then use IF to rename, using exec sp_rename that is executed in only one batch, something like this:

CREATE VIEW VI_ALL_CITIES_AS_CATEGORY_TEMP AS
SELECT PERSONS.FIRST_NAME AS 'Име', PERSONS.LAST_NAME AS 'Фамилия', CITIES.CITY_NAME AS 'Град'
FROM CITIES
LEFT JOIN PERSONS ON CITIES.ID = PERSONS.CITY_ID ;

GO

IF OBJECT_ID('dbo.VI_ALL_CITIES_AS_CATEGORY', 'V') IS NULL
exec sp_rename 'dbo.VI_ALL_CITIES_AS_CATEGORY_TEMP', 'VI_ALL_CITIES_AS_CATEGORY'

GO

IF OBJECT_ID('dbo.VI_ALL_CITIES_AS_CATEGORY_TEMP', 'V') IS NOT NULL
drop view VI_ALL_CITIES_AS_CATEGORY_TEMP

How to check if a view exists and create if it does not

3 Answers3

Linked