删除名称以特定字符串开头的所有表

Question

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

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

Answer 1

如果数据库中有多个所有者，您可能需要修改查询以包含所有者。

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

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

我知道如果我要对生产数据库执行此操作，我会尽可能小心。

编辑代码示例已修复。

Answer 2

SELECT 'DROP TABLE "' + TABLE_NAME + '"' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '[prefix]%'

这将生成一个脚本。

添加子句以在删除之前检查表是否存在：

SELECT 'IF OBJECT_ID(''' +TABLE_NAME + ''') IS NOT NULL BEGIN DROP TABLE [' + TABLE_NAME + '] END;' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '[prefix]%'

Answer 3

这将使您按外键顺序获取表，并避免删除 SQL Server 创建的某些表。 t.Ordinal 值会将表分割为依赖层。

WITH TablesCTE(SchemaName, TableName, TableID, Ordinal) AS
(
    SELECT OBJECT_SCHEMA_NAME(so.object_id) AS SchemaName,
        OBJECT_NAME(so.object_id) AS TableName,
        so.object_id AS TableID,
        0 AS Ordinal
    FROM sys.objects AS so
    WHERE so.type = 'U'
        AND so.is_ms_Shipped = 0
        AND OBJECT_NAME(so.object_id)
        LIKE 'MyPrefix%'

    UNION ALL
    SELECT OBJECT_SCHEMA_NAME(so.object_id) AS SchemaName,
        OBJECT_NAME(so.object_id) AS TableName,
        so.object_id AS TableID,
        tt.Ordinal + 1 AS Ordinal
    FROM sys.objects AS so
        INNER JOIN sys.foreign_keys AS f
            ON f.parent_object_id = so.object_id
                AND f.parent_object_id != f.referenced_object_id
        INNER JOIN TablesCTE AS tt
            ON f.referenced_object_id = tt.TableID
    WHERE so.type = 'U'
        AND so.is_ms_Shipped = 0
        AND OBJECT_NAME(so.object_id)
        LIKE 'MyPrefix%'
)
SELECT DISTINCT t.Ordinal, t.SchemaName, t.TableName, t.TableID
FROM TablesCTE AS t
    INNER JOIN
    (
        SELECT
            itt.SchemaName AS SchemaName,
            itt.TableName AS TableName,
            itt.TableID AS TableID,
            Max(itt.Ordinal) AS Ordinal
        FROM TablesCTE AS itt
        GROUP BY itt.SchemaName, itt.TableName, itt.TableID
    ) AS tt
        ON t.TableID = tt.TableID
            AND t.Ordinal = tt.Ordinal
ORDER BY t.Ordinal DESC, t.TableName ASC

Answer 4

在 Oracle XE 上，这有效：

SELECT 'DROP TABLE "' || TABLE_NAME || '";'
FROM USER_TABLES
WHERE TABLE_NAME LIKE 'YOURTABLEPREFIX%'

或者，如果您还想删除约束并释放空间，请使用：

SELECT 'DROP TABLE "' || TABLE_NAME || '" cascade constraints PURGE;'
FROM USER_TABLES
WHERE TABLE_NAME LIKE 'YOURTABLEPREFIX%'

这将生成一堆 DROP TABLE 级联约束 PURGE 语句...

对于 VIEWS 使用：

SELECT 'DROP VIEW "' || VIEW_NAME || '";'
FROM USER_VIEWS
WHERE VIEW_NAME LIKE 'YOURVIEWPREFIX%'

Answer 5

这是我的解决方案：

SELECT CONCAT('DROP TABLE `', TABLE_NAME,'`;') 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'TABLE_PREFIX_GOES_HERE%';

当然，您需要将 TABLE_PREFIX_GOES_HERE 替换为您的前缀。

Answer 6

当我正在寻找 mysql 语句来删除基于 @Xenph Yan 的所有 WordPress 表时，我看到了这篇文章，这就是我最终所做的：

SELECT CONCAT(  'DROP TABLE `', TABLE_NAME,  '`;' ) AS query
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE  'wp_%'

这将为您提供以 wp_ 开头的所有表的删除查询集

Answer 7

CREATE PROCEDURE usp_GenerateDROP
    @Pattern AS varchar(255)
    ,@PrintQuery AS bit
    ,@ExecQuery AS bit
AS
BEGIN
    DECLARE @sql AS varchar(max)

    SELECT @sql = COALESCE(@sql, '') + 'DROP TABLE [' + TABLE_NAME + ']' + CHAR(13) + CHAR(10)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME LIKE @Pattern

    IF @PrintQuery = 1 PRINT @sql
    IF @ExecQuery = 1 EXEC (@sql)
END

Answer 8

Xenph Yan 的答案比我的干净得多，但这里仍然是我的。

DECLARE @startStr AS Varchar (20)
SET @startStr = 'tableName'

DECLARE @startStrLen AS int
SELECT @startStrLen = LEN(@startStr)

SELECT 'DROP TABLE ' + name FROM sysobjects
WHERE type = 'U' AND LEFT(name, @startStrLen) = @startStr

只需将 tableName 更改为您要搜索的字符即可。

Answer 9

EXEC sp_MSforeachtable 'if PARSENAME("?",1) like ''%CertainString%'' DROP TABLE ?'

编辑：

sp_MSforeachtable 未记录，因此不适合生产，因为它的行为可能因 MS_SQL 版本而异。

Answer 10

如果您的查询返回多行，您可以收集结果并将它们合并到查询中。

declare @Tables as nvarchar(max) = '[schemaName].['
select @Tables =@Tables + TABLE_NAME +'],[schemaName].['
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_SCHEMA = 'schemaName'
AND TABLE_NAME like '%whateverYourQueryIs%'

select @Tables =  Left(@Tables,LEN(@Tables)-13) --trying to remove last ",[schemaName].[" part, so you need to change this 13 with actual lenght 

--print @Tables

declare @Query as nvarchar(max) = 'Drop table ' +@Tables 

--print @Query


exec sp_executeSQL @Query

Answer 11

尝试以下代码：

declare @TableLst table(TblNames nvarchar(500))
insert into @TableLst (TblNames)
SELECT 'DROP TABLE [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'yourFilter%'
WHILE ((select COUNT(*) as CntTables from @TableLst) > 0)
BEGIN
    declare @ForExecCms nvarchar(500) = (select top(1) TblNames from @TableLst)
    EXEC(@ForExecCms)
    delete from @TableLst where TblNames = @ForExecCms
END

此 SQL 脚本在不使用光标的情况下执行。

Answer 12

这对我有用。

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += '
DROP TABLE ' 
    + QUOTENAME(s.name)
    + '.' + QUOTENAME(t.name) + ';'
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s
    ON t.[schema_id] = s.[schema_id] 
    WHERE t.name LIKE 'something%';

PRINT @sql;
-- EXEC sp_executesql @sql;

Answer 13

select 'DROP TABLE ' + name from sysobjects
where type = 'U' and sysobjects.name like '%test%'

-- test 是表名

Answer 14

SELECT 'if object_id(''' + TABLE_NAME + ''') is not null begin drop table "' + TABLE_NAME + '" end;' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '[prefix]%'

Answer 15

我怀疑我必须对 Xenph Yan 的答案进行一些推导，因为我的表不在默认模式中。

SELECT 'DROP TABLE Databasename.schema.' + TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'strmatch%'

Answer 16

如果是临时表，您可能想尝试

SELECT 'DROP TABLE "' + t.name + '"' 
FROM tempdb.sys.tables t
WHERE t.name LIKE '[prefix]%'

Answer 17

我想发布我的解决方案提案，该解决方案将基于通配符（例如“table_20210114”）删除（不仅仅是生成和选择删除命令）所有早于特定天数的表。

DECLARE 
    @drop_command NVARCHAR(MAX) = '',
    @system_time date,
    @table_date nvarchar(8),
    @older_than int = 7
    
Set @system_time = (select getdate() - @older_than)
Set @table_date = (SELECT CONVERT(char(8), @system_time, 112))

SELECT @drop_command += N'DROP TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME([Name]) + ';'
FROM <your_database_name>.sys.tables
WHERE [Name] LIKE 'table_%' AND RIGHT([Name],8) < @table_date

SELECT @drop_command
 
EXEC sp_executesql @drop_command

Answer 18

如果突然需要删除外键链接的表。

USE [CentralIntake]
GO

DECLARE @name VARCHAR(200);
DECLARE @DropForeignKeyProcedure varchar(4000); 
DECLARE @DropTableProcedure varchar(4000); 

/*TEST*/ SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE '%_unused'

DECLARE tb_cursor CURSOR FOR 
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE '%_unused';

OPEN tb_cursor  
FETCH NEXT FROM tb_cursor INTO @name 

WHILE @@FETCH_STATUS = 0  
BEGIN  

/*TEST*/ SELECT 'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']'    FROM sys.foreign_keys WHERE referenced_object_id = object_id(@name)

    DECLARE fk_cursor CURSOR FOR
    (SELECT 'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']'
    FROM sys.foreign_keys
    WHERE referenced_object_id = object_id(@name));

    OPEN fk_cursor
    FETCH NEXT FROM fk_cursor INTO @DropForeignKeyProcedure
    WHILE @@FETCH_STATUS = 0  
        BEGIN   
            EXEC (@DropForeignKeyProcedure);
            FETCH NEXT FROM fk_cursor INTO @DropForeignKeyProcedure
        END
    CLOSE fk_cursor  
    DEALLOCATE fk_cursor

    SET @DropTableProcedure = (SELECT 'DROP TABLE [' +  TABLE_CATALOG  + '].[' + TABLE_SCHEMA + '].[' + @name + ']'
    FROM INFORMATION_SCHEMA.TABLES
    where TABLE_NAME = @name)

    EXEC(@DropTableProcedure)

    FETCH NEXT FROM tb_cursor INTO @name 
END 

CLOSE tb_cursor  
DEALLOCATE tb_cursor