oracle+create+replace,SQL Server equivalent to Oracle's CREATE OR REPLACE VIEW | 易学教程

问题

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?

回答1:

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

回答2:

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

回答3:

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 [ ,...n ] ]

AS select_statement

[ WITH CHECK OPTION ]

[ ; ]

OR ALTER

Conditionally alters the view only if it already exists.

db<>fiddle demo

回答4:

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

回答5:

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 [...]

回答6:

As of SQL Server 2016 you have

DROP TABLE IF EXISTS [foo];

MSDN source

回答7:

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-

回答8:

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.

回答9:

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 ...

来源：https://stackoverflow.com/questions/163246/sql-server-equivalent-to-oracles-create-or-replace-view