SQL Server 2008 删除特殊架构下的所有表

Question

提问by Arbejdsgl?de

Hello I would like to know is is possible to drop all tables in database what was created under custom schema for example DBO1...with one sql query or special script.

您好，我想知道是否可以删除数据库中的所有表，这些表是在自定义模式下创建的，例如 DBO1...使用一个 sql 查询或特殊脚本。

Thanks

谢谢

Answer 1

回答by AdaTheDev

This will generate all the DROP TABLE statements for you and PRINT the SQL statement out. You can then validate it's what you expect before copying and executing. Just make sure you are 100% sure...maybe take a backup first :)

这将为您生成所有 DROP TABLE 语句并将 SQL 语句打印出来。然后，您可以在复制和执行之前验证它是否符合您的预期。只要确保你是 100% 确定...也许先备份:)

DECLARE @SqlStatement NVARCHAR(MAX)
SELECT @SqlStatement = 
    COALESCE(@SqlStatement, N'') + N'DROP TABLE [DBO1].' + QUOTENAME(TABLE_NAME) + N';' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DBO1' and TABLE_TYPE = 'BASE TABLE'

PRINT @SqlStatement

Answer 2

回答by Kevo

Somewhat old thread I know, but I was looking for something like this and found the original answer very helpful. That said, the script will also try to drop views that might exist in that schema and give you an error message because you end up trying to drop a view by issuing a DROP TABLE statement.

我知道有些旧线程，但我一直在寻找类似的东西，发现原始答案非常有帮助。也就是说，脚本还将尝试删除可能存在于该模式中的视图，并给您一条错误消息，因为您最终会尝试通过发出 DROP TABLE 语句来删除视图。

I ended up writing this because I needed to drop all tables, views, procedures and functions from a given schema. Maybe not the most elegant way to accomplish this, but it worked for me and I thought I'd share.

我最终写这篇文章是因为我需要从给定的模式中删除所有表、视图、过程和函数。也许不是实现这一目标的最优雅的方式，但它对我有用，我想我会分享。

DECLARE @Sql VARCHAR(MAX)
      , @Schema varchar(20)

SET @Schema = 'Integration' --put your schema name between these quotes

--tables
SELECT @Sql = COALESCE(@Sql,'') + 'DROP TABLE %SCHEMA%.' + QUOTENAME(TABLE_NAME) + ';' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @Schema
    AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME


--views
SELECT @Sql = COALESCE(@Sql,'') + 'DROP VIEW %SCHEMA%.' + QUOTENAME(TABLE_NAME) + ';' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @Schema
    AND TABLE_TYPE = 'VIEW'
ORDER BY TABLE_NAME

--Procedures
SELECT @Sql = COALESCE(@Sql,'') + 'DROP PROCEDURE %SCHEMA%.' + QUOTENAME(ROUTINE_NAME) + ';' + CHAR(13)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = @Schema
    AND ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME

--Functions
SELECT @Sql = COALESCE(@Sql,'') + 'DROP FUNCTION %SCHEMA%.' + QUOTENAME(ROUTINE_NAME) + ';' + CHAR(13)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = @Schema
    AND ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_NAME


SELECT @Sql = COALESCE(REPLACE(@Sql,'%SCHEMA%',@Schema), '')

PRINT @Sql

Answer 3

回答by raider33

Building on @Kevo's answer, I added the following for dropping all foreign key constraints before deleting the tables. I've only tested on SQL2008 R2

基于@Kevo 的回答，我添加了以下内容，以便在删除表之前删除所有外键约束。我只在 SQL2008 R2 上测试过

select @Sql = COALESCE(@Sql,'') + 'ALTER TABLE %SCHEMA%.' + t.name + ' drop constraint ' + 
OBJECT_NAME(d.constraint_object_id)  + ';' + CHAR(13)
from sys.tables t 
    join sys.foreign_key_columns d on d.parent_object_id = t.object_id 
    inner join sys.schemas s on t.schema_id = s.schema_id
where s.name = @Schema
ORDER BY t.name;

Answer 4

回答by Georgi Yordanov

Building on the other answers, here is a stored procedure spDropSchemathat drops all objects in a schema and the schema itself.

在其他答案的基础上，这里有一个存储过程spDropSchema，它删除架构中的所有对象和架构本身。

Note that the procedure tries to drop sequence objects too, so it will only work on SQL Server 2012 and above.

请注意，该过程也尝试删除序列对象，因此它仅适用于 SQL Server 2012 及更高版本。

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'spDropSchema')
    BEGIN
        DROP  PROCEDURE  spDropSchema
    END
GO

CREATE PROCEDURE spDropSchema(@Schema nvarchar(200))
AS

DECLARE @Sql NVARCHAR(MAX) = '';

--constraints
SELECT @Sql = @Sql + 'ALTER TABLE '+ QUOTENAME(@Schema) + '.' + QUOTENAME(t.name) + ' DROP CONSTRAINT ' + QUOTENAME(f.name)  + ';' + CHAR(13)
FROM sys.tables t 
    inner join sys.foreign_keys f on f.parent_object_id = t.object_id 
    inner join sys.schemas s on t.schema_id = s.schema_id
WHERE s.name = @Schema
ORDER BY t.name;

--tables
SELECT @Sql = @Sql + 'DROP TABLE '+ QUOTENAME(@Schema) +'.' + QUOTENAME(TABLE_NAME) + ';' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @Schema AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

--views
SELECT @Sql = @Sql + 'DROP VIEW '+ QUOTENAME(@Schema) +'.' + QUOTENAME(TABLE_NAME) + ';' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @Schema AND TABLE_TYPE = 'VIEW'
ORDER BY TABLE_NAME

--procedures
SELECT @Sql = @Sql + 'DROP PROCEDURE '+ QUOTENAME(@Schema) +'.' + QUOTENAME(ROUTINE_NAME) + ';' + CHAR(13)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = @Schema AND ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME

--functions
SELECT @Sql = @Sql + 'DROP FUNCTION '+ QUOTENAME(@Schema) +'.' + QUOTENAME(ROUTINE_NAME) + ';' + CHAR(13)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = @Schema AND ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_NAME

--sequences
SELECT @Sql = @Sql + 'DROP SEQUENCE '+ QUOTENAME(@Schema) +'.' + QUOTENAME(SEQUENCE_NAME) + ';' + CHAR(13)
FROM INFORMATION_SCHEMA.SEQUENCES
WHERE SEQUENCE_SCHEMA = @Schema
ORDER BY SEQUENCE_NAME

SELECT @Sql = @Sql + 'DROP SCHEMA '+ QUOTENAME(@Schema) + ';' + CHAR(13)

EXECUTE sp_executesql @Sql

GO

Answer 5

回答by Jurgy

I know this is an old thread but I think the easiest way to do this is by using the undocumented sp_MSforeachtablestored procedure:

我知道这是一个旧线程，但我认为最简单的方法是使用未记录的sp_MSforeachtable存储过程：

EXEC sp_MSforeachtable
  @command1 = 'DROP TABLE ?'
, @whereand = 'AND SCHEMA_NAME(schema_id) = ''your_schema_name'' '

A detailed report on this stored procedure can be found here, but in case the link goes dead here are the highlights:

可以在此处找到有关此存储过程的详细报告，但如果链接失效，此处是亮点：

sp_MSforeachtable is a stored procedure that is mostly used to apply a T-SQL command to every table, iteratively, that exists in the current database.
[...]
realized that the question mark (?) it is used as the replacement of the table and during the execution it will be replaced by the appropriate table name.
@command1, @command2, @command3
sp_MSforeachtable stored procedure requires at least one command to be executed (@command1) but it allows up to 3 commands to be executed. Note that it will start to execute first the @command1 and then @command2 and @command3 by the last and this for each table.
@precommand
Use this parameter to provide a command to be executed before the @command1. It is useful to set variable environments or perform any kind of initialization.
@postcommand
Use this parameter to provide a command to be executed after all the commands being executed successfully. It is useful for control and cleanup processes.
@replacechar
By default, a table is represented by the question mark (?) character. This parameter allows you to change this character.
@whereand
By default, sp_MSforeachtable is applied to all user tables in the database. Use this parameter to filter the tables that you want to work with. On the next section, I will explain how you can filter the tables.

sp_MSforeachtable 是一个存储过程，主要用于将 T-SQL 命令迭代地应用于当前数据库中存在的每个表。
[...]
意识到问号 (?) 它被用作表的替换，并且在执行期间它将被适当的表名替换。
@command1、@command2、@command3
sp_MSforeachtable 存储过程要求至少执行一个命令 (@command1)，但它最多允许执行 3 个命令。请注意，它将首先开始执行@command1，然后是@command2 和@command3，最后和this 对每个表执行。
@precommand
使用此参数提供要在@command1 之前执行的命令。设置变量环境或执行任何类型的初始化都很有用。
@postcommand
使用该参数提供所有命令执行成功后要执行的命令。它对于控制和清理过程很有用。
@replacechar
默认情况下，表由问号 (?) 字符表示。此参数允许您更改此字符。
@whereand
默认情况下， sp_MSforeachtable 应用于数据库中的所有用户表。使用此参数过滤要使用的表。在下一节中，我将解释如何过滤表。

Answer 6

回答by M1K3Y

Also building on @Kevo's answer, I added the following while loop for an issue I was having with TSQL Print statement. A message string can be up to 8,000 characters long. If greater than 8,000 the print statement will truncate any remaining characters.

同样基于@Kevo 的回答，我添加了以下 while 循环来解决我在使用 TSQL Print 语句时遇到的问题。消息字符串最长可达 8,000 个字符。如果大于 8,000，打印语句将截断任何剩余的字符。

DECLARE @SqlLength int
      , @SqlPosition int = 1
      , @printMaxLength int = 8000

SET @SqlLength = LEN(@Sql)

WHILE (@SqlLength) > @printMaxLength
BEGIN
    PRINT SUBSTRING(@Sql, @SqlPosition, @printMaxLength)
    SET @SqlLength = @SqlLength - @printMaxLength
    SET @SqlPosition = @SqlPosition + @printMaxLength
END
IF (@SqlLength) < @printMaxLength AND (@SqlLength) > 0
BEGIN
    PRINT SUBSTRING(@Sql, @SqlPosition, @printMaxLength)
END

Answer 7

回答by chris LB

I combined the answers from @raider33 and @Kevo to one solutions for direct execution.

我将@raider33 和@Kevo 的答案结合到一个直接执行的解决方案中。

DECLARE @SqlStatement NVARCHAR(MAX)
DECLARE @schema varchar(30) = 'SCHEMA_NAME';

select @SqlStatement = COALESCE(@SqlStatement,'') + 'ALTER TABLE '+@schema+'.' + t.name + ' drop constraint ' + 
OBJECT_NAME(d.constraint_object_id)  + ';' + CHAR(13) + CHAR(10)
from sys.tables t 
    join sys.foreign_key_columns d on d.parent_object_id = t.object_id 
    inner join sys.schemas s on t.schema_id = s.schema_id
where s.name = @schema
ORDER BY t.name;

SELECT @SqlStatement += 
    COALESCE(@SqlStatement, '') + 'DROP TABLE ' + @schema +'.'+ QUOTENAME(TABLE_NAME) + ';'  + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @schema

EXECUTE sp_executesql @SqlStatement

Answer 8

回答by Paul Grimshaw

Just in case it helps someone, I added this as a stored procedure to the master database to allow it to conveniently used on any db / schema.

以防万一它对某人有帮助，我将它作为存储过程添加到 master 数据库中，以便它可以方便地用于任何 db/schema。

It can be called like this:

可以这样调用：

EXEC master.dbo.dropTablesInSchema 'my_db', 'dbo

Stored procedure create script:

存储过程创建脚本：

CREATE PROC [master].[dbo].[dropTablesInSchema]
    @db nvarchar(max),
    @schema nvarchar(max)
AS
BEGIN
    DECLARE @Tables TABLE (name nvarchar(max))
    INSERT INTO @Tables
    EXEC ('SELECT TABLE_NAME FROM [' + @db + '].INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ''' + @schema + ''' and TABLE_TYPE =''BASE TABLE''')

    DECLARE @SqlStatement NVARCHAR(MAX)
    SELECT @SqlStatement = 
        COALESCE(@SqlStatement, N'') + N'DROP TABLE [' + @db + '].[' + @schema + '].' + QUOTENAME(NAME) + N';' + CHAR(13)
    FROM @Tables

    EXEC(@SqlStatement)

END

Answer 9

回答by minijag

Building on chris LB's answer, I added

基于克里斯 LB 的回答，我补充说

GROUP BY d.constraint_object_id, t.name

because I saw duplicate constraint deletions in my query. constraint_object_idis the FK Constraint ID, as noted at https://msdn.microsoft.com/en-us/library/ms186306.aspx

因为我在查询中看到了重复的约束删除。 constraint_object_id是 FK 约束 ID，如https://msdn.microsoft.com/en-us/library/ms186306.aspx 所述

DECLARE @SqlStatement NVARCHAR(MAX),
        @Schema NVARCHAR(20)

SET @Schema = 'aa'

SELECT @SqlStatement = 
    COALESCE(@SqlStatement,'') + 'ALTER TABLE '+@Schema+'.' + t.name + ' DROP CONSTRAINT ' + 
    OBJECT_NAME(d.constraint_object_id)  + ';' + CHAR(13) + CHAR(10)
FROM sys.tables t
    JOIN sys.foreign_key_columns d on t.object_id = d.parent_object_id 
    INNER JOIN sys.schemas s on t.schema_id = s.schema_id
WHERE s.name = @Schema
GROUP BY d.constraint_object_id, t.name
ORDER BY t.name;

Answer 10

回答by sandba

select 'DROP TABLE [TABSCHEMA].' + QUOTENAME(TABLE_NAME) + N';' from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'TABSCHEMA' and TABLE_TYPE = 'BASE TABLE'

SQL Server 2008 删除特殊架构下的所有表

提问by Arbejdsgl?de

回答by AdaTheDev

回答by Kevo

回答by raider33

回答by Georgi Yordanov

回答by Jurgy

回答by M1K3Y

回答by chris LB

回答by Paul Grimshaw

回答by minijag

回答by sandba

相关推荐

最近更新

标签

SQL Server 2008 删除特殊架构下的所有表

提问by Arbejdsgl?de

回答by AdaTheDev

回答by Kevo

回答by raider33

回答by Georgi Yordanov

回答by Jurgy

回答by M1K3Y

回答by chris LB

回答by Paul Grimshaw

回答by minijag

回答by sandba

相关推荐

如何将具有换行符的 SQL 值转换为 HTML 中的适当段落 <p>？

SQL 如何选择每个部门的最高工资，包括获得它的员工

SQL EXISTS 在 Oracle 中如何工作，它与 IN 有何不同？

如何在 SQL 中从两个不同的表中选择值

相关推荐

最近更新

标签