SQL Server相当于Oracle的CREATE OR REPLACE VIEW

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

CREATE OR REPLACE VIEW MY_VIEW AS
SELECT SOME_FIELD
FROM SOME_TABLE
WHERE SOME_CONDITIONS

正如语法所暗示的那样,这将删除旧视图并使用我给出的任何定义重新创建它.

MSSQL(SQL Server 2005或更高版本)中是否有相同的功能可以执行相同的操作？

1> johndacostaa..：

---

上述解决方案虽然可以完成工作,但却有可能降低用户权限.我更喜欢按如下方式创建或替换视图或存储过程.

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

---

我曾经是"Drop"然后(重新)"添加"的人.但现在我倾向于这种类型的解决方案(如果没有,则添加,然后改变).

---

对于SQL Server 2016 SP1 +,请参阅lad2025的回答.

2> DaveK..：

---

您可以使用"IF EXISTS"来检查视图是否存在,如果存在则丢弃.

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

CREATE VIEW MyView
AS 
     ....
GO

---

这样做的问题是您丢失了丢弃的对象上可能存在的任何权限.

3> Lukasz Szozd..：

---

SQL Server 2016 SP1+您可以使用CREATE OR ALTER VIEW语法作为参考.

MSDN创建视图:

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

或者改变

只有在视图已存在的情况下才有条件地改变视图.

db <>小提琴演示

---

@DivTiwari,这在SQL 2016中显然是有效的.它只需要SQL Server 11年才能赶上这个oracle功能,显然:)

4> Tom..：

---

我用:

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

我认为它使我的changecripts更具可读性

5> Michael Petr..：

---

我通常使用这样的东西:

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> Justin Deari..：

---

从SQL Server 2016开始

DROP TABLE IF EXISTS [foo];

MSDN来源