从Sql Server中删除所有视图

Question

使用以下语句在 SQL Server 中：

EXEC sp_msforeachtable 'DROP TABLE ?'

我知道可以一次性删除所有表格。

是否有类似于此的语句可以删除视图？我尝试了下面这个，希望能有所收获： EXEC sp_msforeachview 'DROP VIEW ?'，但是它不起作用！

- Sam

---

你尝试过执行 "EXEC sp_msforeachTABLE 'DROP VIEW ?'" 吗？ - Bishnu Paudel

Answer 1

以下是无需使用光标的代码：

DECLARE @sql VARCHAR(MAX) = ''
        , @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ;

SELECT @sql = @sql + 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(v.name) +';' + @crlf
FROM   sys.views v

PRINT @sql;
EXEC(@sql);

- Yaroslav

---

如果您在另一个模式（而非dbo）中拥有视图，则此脚本将无法正常运行。 - MikeTeeVee

如果您的名称中有特殊字符，它也会出现错误。 - MikeTeeVee

添加一些方括号可以帮助处理空格、特殊字符等。从sys.views中选择@sql=@sql+'DROP VIEW ['+name +'];'。 - user1316401

这要看情况，如果你使用“绑定模式”，你会遇到麻烦...请参考我的答案获取解决方案。 - Ric .Net

我喜欢这种简单的循环解决方案，相比其他各种循环解决方案，它可能不是完美的，但我仍然认为它很棒！谢谢分享。 - Simon

Answer 2

declare @SQL nvarchar(max)

set @SQL = 
  (
  select 'drop view '+name+'; '
  from sys.views
  for xml path('')
  )

exec (@SQL)

- Mikael Eriksson

Answer 3

我希望有一个脚本可以按正确的依赖顺序删除模式绑定视图，并且我希望它可以在SQL Azure上运行，因为sys.dm_sql_referencing_entities在那里不可用。我还想在实际运行之前查看要运行的SQL - 这是@RicNet答案中的脚本无法做到的。因此，我编写了这个递归查询，以其他答案作为基础。

DECLARE @sql VARCHAR(MAX) = ''
DECLARE @crlf VARCHAR(2) = CHAR(13) + CHAR(10);

;WITH allviews as
( --just combining schema and name
SELECT
    object_id,
    '[' + SCHEMA_NAME(schema_id) + '].[' + name + ']' AS viewname
FROM sys.views
),
dependents AS
( 
SELECT
    referencing.viewname dependentname,
    referenced.viewname dependenton
FROM sys.sql_expression_dependencies r
    INNER JOIN allviews referencing
        ON referencing.object_id = r.referencing_id
    INNER JOIN allviews referenced
        ON referenced.object_id = r.referenced_id
)
,
nodependents 
AS
( 
SELECT
    viewname name
FROM allviews v
    LEFT JOIN dependents d
        ON d.dependentname = viewname
WHERE d.dependentname IS NULL
)
,hierarchy AS
( --the hierarchy recurses the dependencies
SELECT
    d.dependenton,
    d.dependentname,
    1 tier
FROM dependents d UNION ALL SELECT
    d.dependenton,
    d.dependentname,
    h.tier + 1
FROM dependents d
    INNER JOIN hierarchy h
        ON h.dependenton = d.dependentname
--best thing I could think to stop the recursion was to 
--stop when we reached an item with no dependents       
WHERE h.dependenton NOT IN (SELECT
    name
FROM nodependents)
    ),
combined as
( --need to add item with no dependents back in
SELECT
    0 tier,
    name
FROM nodependents UNION SELECT
    tier,
    dependentname
FROM hierarchy  
)
SELECT
    @sql = @sql + 'DROP VIEW ' + name + ';' + @crlf
FROM combined
GROUP BY name --need to group because of multiple dependency paths
ORDER BY MAX(tier) desc

PRINT @sql;

--commented out until I'm confident I want to run it
--EXEC(@sql)

- Colin

Answer 4

所有答案都没有考虑视图之间的约束条件。这个脚本会考虑到这一点：

SET @schemeName = 'dbo'

SELECT @name = 
(SELECT TOP 1 o.[name] 
 FROM sysobjects o
 inner join sys.views v ON o.id = v.object_id
 WHERE SCHEMA_NAME(v.schema_id) =@schemeName AND o.[type] = 'V' AND o.category = 0 AND o.[name] NOT IN
 (
    SELECT  referenced_entity_name
    FROM sys.sql_expression_dependencies AS sed
    INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
    WHERE referenced_schema_name = @schemeName
 )
 ORDER BY [name])

 WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [' + @schemeName + '].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
SELECT @name = 
(SELECT TOP 1 o.[name] 
 FROM sysobjects o
 inner join sys.views v ON o.id = v.object_id
 WHERE SCHEMA_NAME(v.schema_id) = @schemeName AND o.[type] = 'V' AND o.category = 0 AND o.[name] NOT IN
 (
    SELECT  referenced_entity_name
    FROM sys.sql_expression_dependencies AS sed
    INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
    WHERE referenced_schema_name = @schemeName
 )
 ORDER BY [name])
END
GO

此代码循环遍历所有视图，并选择不在引用系统表中的顶部1个视图。

- Ric .Net

Answer 5

但是模式呢？
如果视图属于模式，则下面的脚本将对您有所帮助

DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+'DROP VIEW '+name +';' FROM 
(
SELECT Name=[s].name  + '.' + [v].name FROM sys.views [v]
LEFT OUTER JOIN sys.schemas [s]
ON
(
    [v].[schema_id]=[s].[schema_id]
)
)
X
EXEC(@sql)

- Appyks

Answer 6

试试这个脚本

DECLARE @viewName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type = 'v'
      OPEN cur

      FETCH NEXT FROM cur INTO @viewName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP VIEW ' + @viewName)
            FETCH NEXT FROM cur INTO @viewName
      END
      CLOSE cur
      DEALLOCATE cur

点击此处查看更多信息

- Prince Jea

Answer 7

由于我尝试的所有脚本答案在多个模式的情况下都不能正确工作，因此我包含了一个有效的脚本。

--DBNAME, 放入您自己的数据库名称 use SIPE_ISU

声明@viewName varchar(500) 声明cur游标以选择sk.name + '.'+so.name从sys.objects so内连接sys.schemas sk，其中sk.schema_id = so.schema_id且类型='v' 打开cur 从cur中获取下一个@viewName 当@@fetch_status = 0时 开始 执行('DROP VIEW ' + @viewName) 从cur中获取下一个@viewName 结束 关闭cur DEALLOCATE cur

- A. Lion