删除所有名称以特定字符串开头的表 [英] Drop all tables whose names begin with a certain string

问题描述

如何删除所有名称以给定字符串开头的表？

How can I drop all tables whose names begin with a given string?

我认为可以通过一些动态SQL和 INFORMATION_SCHEMA 表来完成。

I think this can be done with some dynamic SQL and the INFORMATION_SCHEMA tables.

推荐答案

如果数据库中存在多个查询，则可能需要修改查询以包括所有者。

You may need to modify the query to include the owner if there's more than one in the database.

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'prefix%'

OPEN cmds
WHILE 1 = 1
BEGIN
    FETCH cmds INTO @cmd
    IF @@fetch_status != 0 BREAK
    EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

这比使用生成脚本并运行的两步方法更干净。但是脚本生成的一个优点是，它使您有机会在实际运行之前检查要运行的全部内容。

This is cleaner than using a two-step approach of generate script plus run. But one advantage of the script generation is that it gives you the chance to review the entirety of what's going to be run before it's actually run.

我知道如果我

Edit 代码示例已修复。

这篇关于删除所有名称以特定字符串开头的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！