SQL Server equivalent to Oracle's CREATE OR REPLACE VIEW

在 Oracle 中，我可以用一条语句重新创建一个视图，如下所示:

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.

在 MSSQL (SQLServer2005或更高版本)中是否有等效的方法来做同样的事情？

146675 次浏览
小开

您可以使用“ IF EXISTS”检查视图是否存在，如果存在则删除。

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'MyView')
DROP VIEW MyView
GO


CREATE VIEW MyView
AS
....
GO
小开

我通常使用这样的东西:

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

您可以使用 ALTER 更新视图，但这与 Oracle 命令不同，因为它只在视图已经存在时才能工作。也许戴维的回答更好，因为这将永远工作。

小开

我用:

IF OBJECT_ID('[dbo].[myView]') IS NOT NULL
DROP VIEW [dbo].[myView]
GO
CREATE VIEW [dbo].[myView]
AS

...

最近我为这类东西增加了一些实用程序:

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

所以我现在开始写作

EXEC dbo.DropView 'mySchema', 'myView'
GO
CREATE View myView
...
GO

我认为它使我的变更脚本更具可读性

小开
最佳答案

上面的解决方案虽然可以完成任务，但是有可能会丢失用户权限。我喜欢按照以下方式创建或替换视图或存储过程。

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
小开

As of SQL Server 2016 you have 

DROP TABLE IF EXISTS [foo];

MSDN 来源

小开

为了参考 SQL Server 2016 SP1+，可以使用 CREATE OR ALTER VIEW语法。

MSDN 创建视图 :

CREATE [ OR ALTER ] VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ]
[ ; ]

OR ALTER

Conditionally alters the view only if it already exists.

Db < > 小提琴演奏

小开

它在 SQLServer2017上运行良好:

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-

小开

在 SQLServer2016(或更新版本)中，您可以使用以下命令:

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

提交答案