How to make CREATE OR REPLACE VIEW work in SQL Server?

This question already has an answer here:

  • Sql Server equivalent to Oracle's CREATE OR REPLACE VIEW

    7 answers

  • Is timestampdiff() in MySQL equivalent to datediff() in SQL Server?
  • Does SQL Server creates Non clustered index on all columns of a table by default
  • Difference between IS NULL criteria in JOIN and WHERE in a query
  • WHERE clause on SQL Server “Text” data type
  • SQLConnection connection error “Login failed for user server/guest”
  • Help me do this SQL update query correctly
  • 9 Solutions collect form web for “How to make CREATE OR REPLACE VIEW work in SQL Server?”

    Edit: Although this question has been marked as a duplicate, it has still been getting attention. The answer provided by @JaKXz is correct and should be the accepted answer.


    You’ll need to check for the existence of the view. Then do a CREATE VIEW or ALTER VIEW depending on the result.

    IF OBJECT_ID('dbo.data_VVVV') IS NULL
    BEGIN
        CREATE VIEW dbo.data_VVVV
        AS
        SELECT VCV.xxxx, VCV.yyyy AS yyyy, VCV.zzzz AS zzzz FROM TABLE_A VCV
    END
    ELSE
        ALTER VIEW dbo.data_VVVV
        AS
        SELECT VCV.xxxx, VCV.yyyy AS yyyy, VCV.zzzz AS zzzz FROM TABLE_A VCV
    BEGIN
    END
    

    Borrowing from @Khan’s answer, I would do:

    IF OBJECT_ID('dbo.test_abc_def', 'V') IS NOT NULL
        DROP VIEW dbo.test_abc_def
    GO
    
    CREATE VIEW dbo.test_abc_def AS
    SELECT 
        VCV.xxxx
        ,VCV.yyyy AS yyyy
        ,VCV.zzzz AS zzzz
    FROM TABLE_A
    

    MSDN Reference

    Here is another method, where you don’t have to duplicate the contents of the view:

    IF (NOT EXISTS (SELECT 1 FROM sys.views WHERE name = 'data_VVV'))
    BEGIN
        EXECUTE('CREATE VIEW data_VVVV as SELECT 1 as t');
    END;
    
    ALTER VIEW data_VVVV AS 
        SELECT VCV.xxxx, VCV.yyyy AS yyyy, VCV.zzzz AS zzzz FROM TABLE_A ;
    

    The first checks for the existence of the view (there are other ways to do this). If it doesn’t exist, then create it with something simple and dumb. If it does, then just move on to the alter view statement.

    SQL Server 2016 Answer

    With SQL Server 2016 you can now do (MSDN Source):

    DROP VIEW IF EXISTS dbo.MyView
    

    The accepted solution has an issue with the need to maintain the same statement twice, it isnt very efficient (although it works). In theory Gordon Linoff’s solution would be the go, except it does not work in MSSQL because create view must be the first line in a batch.

    The drop/create does not answer the question as posed.
    The following does the job as per the original question.

    if not exists (select * from sysobjects where name='TABLE_A' and xtype='V')
    exec ('create view SELECT 
    VCV.xxxx,
        VCV.yyyy AS yyyy,
        VCV.zzzz AS zzzz
    FROM TABLE_A')
    

    How about something like this, comments should explain:

    --DJ - 2015-07-15 Example for view CREATE or REPLACE
    
    --Replace with schema and view names
    DECLARE @viewName NVARCHAR(30)= 'T';
    DECLARE @schemaName NVARCHAR(30)= 'dbo';
    
    --Leave this section as-is
    BEGIN TRY
        DECLARE @view AS NVARCHAR(100) = '
    CREATE VIEW ' + @schemaName + '.' + @viewName + ' AS SELECT '''' AS [1]';
        EXEC sp_executesql
            @view;
    END TRY
    BEGIN CATCH
        PRINT 'View already exists';
    END CATCH;
    GO
    
    --Put full select statement here after modifying the view & schema name appropriately
    ALTER VIEW [dbo].[T]
    AS
        SELECT  '' AS [2];
    GO
    
    
    --Verify results with select statement against the view
    SELECT  *
    FROM    [T];
    

    Cheers
    -DJ

    Altering a view could be accomplished by dropping the view and recreating it. Use the following to drop and recreate your view.

    IF EXISTS
    (SELECT NAME FROM SYS.VIEWS WHERE NAME = 'dbo.test_abc_def')
    DROP VIEW dbo.test_abc_def) go
    
    CREATE VIEW dbo.test_abc_def AS
    SELECT 
        VCV.xxxx, 
        VCV.yyyy AS yyyy
        ,VCV.zzzz AS zzzz
    FROM TABLE_A
    

    To create a view in SQL, following is the syntax:

     CREATE VIEW view_name AS
    

    and to alter the view syntax is :

     ALTER VIEW view_name as
    
    IF NOT EXISTS(select * FROM sys.views where name = 'data_VVVV ')
        BEGIN
            CREATE VIEW data_VVVV AS 
            SELECT VCV.xxxx, VCV.yyyy AS yyyy, VCV.zzzz AS zzzz FROM TABLE_A VCV
        END
    ELSE
        BEGIN
            ALTER VIEW data_VVVV AS 
            SELECT VCV.xxxx, VCV.yyyy AS yyyy, VCV.zzzz AS zzzz FROM TABLE_A VCV
        END
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.