SQL Server 相当于 Oracle 的 CREATE OR REPLACE VIEW [英] SQL Server equivalent to Oracle's CREATE OR REPLACE VIEW

问题描述

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

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.

在 MSSQL(SQL Server 2005 或更高版本)中是否有可以做同样事情的等价物?

Is there an equivalent in MSSQL (SQL Server 2005 or later) that will do the same thing?

推荐答案

上述解决方案虽然可以完成工作，但这样做的风险是放弃用户权限.我更喜欢按如下方式创建或替换视图或存储过程.

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

这篇关于SQL Server 相当于 Oracle 的 CREATE OR REPLACE VIEW的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！