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

Drop all tables whose names begin with a certain string

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

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

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

编辑代码示例已修复。

1
2
3

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

这将生成一个脚本。

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

1
2
3

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

这将使您获得按外键顺序排列的表，并避免删除SQL Server创建的某些表。 t.Ordinal值会将表切成依赖关系层。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

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

在Oracle XE上，这有效：

1
2
3

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

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

1
2
3

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

这将生成一堆DROP TABLE cascade constraints PURGE语句...

对于VIEWS，请使用以下命令：

1
2
3

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

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

编辑：

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

这是我的解决方案：

1
2
3

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

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

我在寻找mysql语句以删除基于@Xenph Yan的所有WordPress表时看到的帖子，这是我最终要做的事情：

1
2
3

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

Xenph Yan的答案比我的答案干净得多，但这里的答案完全一样。

1
2
3
4
5
6
7
8

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更改为您要搜索的字符。

这对我有用。

1
2
3
4
5
6
7
8
9
10
11
12
13

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;

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

1
2
3

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

我怀疑我对Xenph Yan的答案做了些微推导，因为我的表不在默认模式中。

1
2
3

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

1
2
3

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