删除名称以某个字符串开头的所有表

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]%'

阎学通的回答比我的要干净得多，但这里还是我的回答。

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

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

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

这比使用两步生成脚本加上 run 的方法更简单。但是脚本生成的一个优点是，它让您有机会在实际运行之前检查将要运行的所有内容。

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

编辑 代码示例固定。

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

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

—— Test 是表名

这将使表按外键顺序排列，并避免删除 SQLServer 创建的一些表。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

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

我不得不对 XenphYan 的答案做一个小小的推导，我怀疑这是因为我的表不在默认模式中。

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

在 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 cascade constraints PURGE语句..。

对于 VIEWS，使用以下方法:

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

当我在寻找 mysql 语句以删除所有基于@Xenph Yan 的 WordPress 表格时，我看到了这篇文章，下面是我最终做的:

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

这将为以 wp _ 开头的所有表提供一组拖放查询

我的解决办法是:

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

当然，您需要用前缀替换 TABLE_PREFIX_GOES_HERE。

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

编辑:

Sp _ MSforeachtable 没有文档记录，因此不适合生产，因为它的行为可能因 MS _ SQL 版本而异。

对于临时表，您可能需要尝试使用

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

这招对我很管用。

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;

我想张贴我的解决方案的建议，DROP (不只是生成和选择一个拖放命令)所有的表基于通配符(例如“ 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

如果查询返回多行，则可以收集结果并将其合并到查询中。

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

尝试以下代码:

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 脚本时不使用 光标。