How to make CREATE OR REPLACE VIEW work in SQL Server? [duplicate]
Borrowing from @Khan's answer, I would do:
IF OBJECT_ID('dbo.test_abc_def', 'V') IS NOT NULL DROP VIEW dbo.test_abc_defGOCREATE VIEW dbo.test_abc_def ASSELECT VCV.xxxx ,VCV.yyyy AS yyyy ,VCV.zzzz AS zzzzFROM TABLE_A
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;GOALTER 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
Or alternatively (MSDN Source):
CREATE OR ALTER VIEW dbo.MyView