如何删除除1个表之外的所有表

Question

我有一堆数据库表，我想保留其中一个并删除其他所有表。我的脚本如下...但是当我运行我的尝试时，我创建了一个无限循环。

您有什么想法吗？

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects 
                WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL 
IF @name !=  'tableNotToBeDropped'
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects  
                    WHERE [type] = 'U' AND category = 0 AND [name] > @name 
                    ORDER BY [name])
END
GO

- Fearghal

Answer 1

完全没有必要使用循环。 DROP TABLE 可以通过一条语句删除多个表：

DECLARE @tables NVARCHAR(MAX) = 
         STUFF((SELECT ',' + QUOTENAME([table_name]) AS [text()]  
               FROM INFORMATION_SCHEMA.TABLES
               WHERE table_schema = 'dbo' 
               AND TABLE_NAME <> 'tableNotToBeDropped'
               FOR XML PATH('')),1,1,'');
               
DECLARE @sql NVARCHAR(MAX) = 'DROP TABLE ' + @tables;

-- debug
SELECT @sql;

EXEC sp_executesql @sql;

实时演示

工作原理：

1. 从特定模式中获取除一个之外的所有表名
2. 创建逗号分隔列表
3. 删除表

请记住，如果您的表定义了外键，则删除顺序很重要。

---

编辑：

SQL Server 2017版本：

DECLARE @sql NVARCHAR(MAX) = 'DROP TABLE IF EXISTS ' 
            + (SELECT string_agg(QUOTENAME(table_name), ',')
               FROM INFORMATION_SCHEMA.TABLES
               WHERE table_schema = 'dbo' 
               AND TABLE_NAME <> 'tableNotToBeDropped'
               AND TABLE_NAME LIKE 't%');

SELECT @sql;
EXEC sp_executesql @sql;

DBFiddle Demo

- Lukasz Szozda

Answer 2

这个一行代码也可以工作：

sp_MSforeachtable @command1='IF OBJECT_ID(''?'') NOT IN (OBJECT_ID(''TableToNotDelete'')) DROP TABLE ?'

- Ross Bush

---

这只是使用游标的另一种方式。这里不需要循环。而且这是未记录的。 ;) - Sean Lange

Answer 3

我建议您改用游标，并在游标查询的where子句中添加名称不等于条件。

http://www.kodyaz.com/articles/sql-cursor-example-tables-rows-count-using-sql-server-cursor.aspx

- Joe C

---

这种情况下不需要任何类型的循环。 - Sean Lange

他试图做一个循环。我知道这并不需要，但有些人不喜欢for xml语法。 - Joe C

你可以用其他方法来完成这个任务，而不必使用循环。大多数人不知道如何在没有循环的情况下完成这种任务，当他们被展示了新技巧后通常会感到非常兴奋。 - Sean Lange