在 SQL Server 数据库中删除属于不同模式的所有对象？

Question

在 SQL Server 数据库中删除属于不同模式的所有对象？

8

有没有一种方法可以删除一个数据库中属于两个不同模式的所有对象？

我之前一直在使用一个模式，所以我使用以下查询语句来查找所有对象：

Select * From sysobjects Where type=...

然后我放弃了我所使用的所有东西

Drop Table ...

现在我引入了另一个模式，每次尝试删除它时，都会提示我没有权限或对象不存在。但是，如果我在对象前面加上 [schema.object]，就可以正常工作。我不知道如何自动化这个过程，因为我不知道对象属于哪个模式。有人知道如何删除数据库中属于任何模式的所有对象吗？

（使用的用户是两个模式的所有者，数据库中的对象由该用户创建，以及正在删除对象的用户 - 如果我使用的前缀为Drop Table Schema1.blah则可以正常工作）

- Captain Alizee

1

提示：sysschemas。实际上：sys.schemas - Mitch Wheat

4个回答

3

其他问题似乎都没有试图回答问题中的所有对象部分。

我很惊讶你必须自己处理它 - 我希望有一个“drop schema blah cascade”的选项。毕竟，每个设置开发服务器的人都必须这样做，在能够进行常规编程之前进行一些元编程是非常可怕的。不管怎样... rant结束！

我开始查看一些文章，以清除模式的方式进行操作：有一篇旧文章介绍了如何做到这一点，但其中提到的表现在已被标记为过时。我还查看了新表的文档，以帮助理解这里正在发生什么。

还有另一个答案和一个很棒的动态SQL资源链接到它。

看了这么多东西后，它似乎有点太乱了。

我认为更好的选择是去做

ALTER DATABASE 'blah' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
drop database 'blah'

create database 'blah'

相反，使用以下命令。顶部的额外咒语基本上是为了强制删除数据库，如此处所述。

感觉有点不对，但编写删除脚本所涉及的复杂性是避免它的一个很好的理由。

如果删除数据库时出现问题，我可能会重新查看一些链接并发布另一个答案。

- JonnyRaa

1

我完全同意你对于缺少drop schema cascade的看法。这个答案让我相信了停止搜索并清空整个数据库的正确性。 - flodin

@flodin 是的，一开始感觉有点不对劲，但自从那以后我就没有回头看过了。在 SQL Server 中拆除和创建新数据库并不是一项昂贵的操作。很高兴能够帮到你！ - JonnyRaa

2

尝试在SQL2012或以上版本上使用此脚本，

此脚本可以帮助您删除所选架构下的所有对象。注意：以下脚本是针对dbo架构中的所有对象，但您可以在第一行@MySchemaName中进行更改。

最初的回答：

DECLARE @MySchemaName VARCHAR(50)='dbo', @sql VARCHAR(MAX)='';
DECLARE @SchemaName VARCHAR(255), @ObjectName VARCHAR(255), @ObjectType VARCHAR(255), @ObjectDesc VARCHAR(255), @Category INT;

DECLARE cur CURSOR FOR
    SELECT  (s.name)SchemaName, (o.name)ObjectName, (o.type)ObjectType,(o.type_desc)ObjectDesc,(so.category)Category
    FROM    sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    INNER JOIN sysobjects so ON so.name=o.name
    WHERE s.name = @MySchemaName
    AND so.category=0
    AND o.type IN ('P','PC','U','V','FN','IF','TF','FS','FT','PK','TT')

OPEN cur
FETCH NEXT FROM cur INTO @SchemaName,@ObjectName,@ObjectType,@ObjectDesc,@Category

SET @sql='';
WHILE @@FETCH_STATUS = 0 BEGIN    
    IF @ObjectType IN('FN', 'IF', 'TF', 'FS', 'FT') SET @sql=@sql+'Drop Function '+@MySchemaName+'.'+@ObjectName+CHAR(13)
    IF @ObjectType IN('V') SET @sql=@sql+'Drop View '+@MySchemaName+'.'+@ObjectName+CHAR(13)
    IF @ObjectType IN('P') SET @sql=@sql+'Drop Procedure '+@MySchemaName+'.'+@ObjectName+CHAR(13)
    IF @ObjectType IN('U') SET @sql=@sql+'Drop Table '+@MySchemaName+'.'+@ObjectName+CHAR(13)

    --PRINT @ObjectName + ' | ' + @ObjectType
    FETCH NEXT FROM cur INTO @SchemaName,@ObjectName,@ObjectType,@ObjectDesc,@Category
END
CLOSE cur;    
DEALLOCATE cur;
SET @sql=@sql+CASE WHEN LEN(@sql)>0 THEN 'Drop Schema '+@MySchemaName+CHAR(13) ELSE '' END
PRINT @sql
EXECUTE (@sql)

- Haseeb

0

我不知道你使用的Sql Server版本是哪个，但假设它是2008或更高版本，也许以下命令非常有用（请检查是否可以一行代码删除所有表）：

 sp_MSforeachtable "USE DATABASE_NAME DROP TABLE ?"

这个脚本将会在数据库DATABASE_NAME中执行DROP TABLE ....命令。非常简单且完美运行。该命令也可以用于执行其他的SQL指令，例如：

sp_MSforeachtable "USE DATABASE_NAME SELECT * FROM ?"

- Gaston Flores

1

use 命令用于更改当前的 数据库，而非当前模式（我甚至不确定 SQL Server 中是否存在“当前”模式的概念）。 - user330315

@a_horse_with_no_name 对不起，是我的错误，我已经更正了我的帖子。谢谢。 - Gaston Flores

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Bryan · Accepted Answer

结合 sys.objects 和 OBJECT_SCHEMA_NAME 来构建你的 DROP TABLE 语句，检查后再复制/粘贴以执行：

SELECT 'DROP TABLE ' +
       QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' +
       QUOTENAME(name) + ';'
  FROM sys.objects
 WHERE type_desc = 'USER_TABLE';

或者使用sys.tables来避免需要使用type_desc过滤器:

SELECT 'DROP TABLE ' +
       QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' +
       QUOTENAME(name) + ';'
  FROM sys.tables;

SQL Fiddle