SQL Server equivalent to Oracle s CREATE OR REPLACE VIEW

Question

In Oracle  I can re-create a view with a single statement  as shown here   CREATE OR REPLACE VIEW MY VIEW AS SELECT SOME FIELD FROM SOME TABLE WHERE SOME CONDITIONS   As the syntax implies  this will drop the old view and re-create it with whatever definition I ve given   Is there an equivalent in MSSQL  SQL Server 2005 or later  that will do the same thing

User · Answer

I use   IF OBJECT ID   dbo   myView    IS NOT NULL DROP VIEW  dbo   myView  GO CREATE VIEW  dbo   myView  AS        Recently I added some utility procedures for this kind of stuff   CREATE PROCEDURE dbo DropView  ASchema VARCHAR 100    AView VARCHAR 100  AS BEGIN   DECLARE  sql VARCHAR 1000     IF OBJECT ID        ASchema            AView        IS NOT NULL   BEGIN     SET  sql     DROP VIEW            ASchema            AView             EXEC  sql     END  END   So now I write  EXEC dbo DropView  mySchema    myView  GO CREATE View myView     GO   I think it makes my changescripts a bit more readable

User · Answer

For reference from SQL Server 2016 SP1  you could use CREATE OR ALTER VIEW syntax   MSDN CREATE VIEW  CREATE   OR ALTER   VIEW   schema name     view name    column       n            WITH  lt view attribute gt        n        AS select statement      WITH CHECK OPTION             OR ALTER Conditionally alters the view only if it already exists   db lt  gt fiddle demo

User · Answer

It works fine for me on SQL Server 2017   USE MSSQLTipsDemo  GO CREATE OR ALTER PROC CreateOrAlterDemo AS BEGIN SELECT TOP 10   FROM  dbo   CountryInfoNew  END GO   https   www mssqltips com sqlservertip 4640 new-create-or-alter-statement-in-

User · Answer

You can use ALTER to update a view  but this is different than the Oracle command since it only works if the view already exists  Probably better off with DaveK s answer since that will always work

User · Answer

The solutions above though they will get the job done do so at the risk of dropping user permissions  I prefer to do my create or replace views or stored procedures as follows   IF NOT EXISTS  SELECT   FROM sys views WHERE object id   OBJECT ID N  dbo   vw myView         EXEC sp executesql N CREATE VIEW  dbo   vw myView  AS SELECT   This is a code stub which will be replaced by an Alter Statement   as  code stub   GO  ALTER VIEW  dbo   vw myView  AS SELECT  This is a code which should be replaced by the real code for your view  as  real code  GO

User · Answer

I typically use something like this   if exists  select   from dbo sysobjects   where id   object id N dbo MyView   and   OBJECTPROPERTY id  N IsView     1  drop view dbo MyView go create view dbo MyView

User · Answer

In SQL Server 2016  or newer  you can use this    CREATE OR ALTER VIEW VW NAMEOFVIEW AS       In older versions of SQL server you have to use something like  DECLARE  script NVARCHAR MAX    N VIEW  dbo   VW NAMEOFVIEW  AS        IF NOT EXISTS SELECT   FROM sys views WHERE name    VW NAMEOFVIEW   -- IF OBJECT ID   dbo   VW NAMEOFVIEW    IS NOT NULL BEGIN EXEC  CREATE      script  END ELSE BEGIN EXEC  ALTER      script  END   Or  if there are no dependencies on the view  you can just drop it and recreate    IF EXISTS SELECT   FROM sys views WHERE name    VW NAMEOFVIEW   -- IF OBJECT ID   dbo   VW NAMEOFVIEW    IS NOT NULL BEGIN     DROP VIEW  VW NAMEOFVIEW   END  CREATE VIEW  VW NAMEOFVIEW  AS

User · Answer

You can use  IF EXISTS  to check if the view exists and drop if it does    IF EXISTS  SELECT TABLE NAME FROM INFORMATION SCHEMA VIEWS         WHERE TABLE NAME    MyView       DROP VIEW MyView GO  CREATE VIEW MyView AS            GO

User · Answer

As of SQL Server 2016 you have   DROP TABLE IF EXISTS  foo     MSDN source

[sql-server] SQL Server equivalent to Oracle's CREATE OR REPLACE VIEW

Examples related to sql-server