SQL 删除名称以特定字符串开头的所有表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4393/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Drop all tables whose names begin with a certain string
提问by Blorgbeard is out
How can I drop all tables whose names begin with a given string?
如何删除名称以给定字符串开头的所有表？
I think this can be done with some dynamic SQL and the INFORMATION_SCHEMAtables.
我认为这可以通过一些动态 SQL 和INFORMATION_SCHEMA表来完成。
回答by Curt Hagenlocher
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.
这比使用生成脚本加运行的两步方法更简洁。但是脚本生成的一个优点是它让您有机会在实际运行之前查看将要运行的全部内容。
I know that if I were going to do this against a production database, I'd be as careful as possible.
我知道如果我要对生产数据库执行此操作，我会尽可能小心。
EditCode sample fixed.
编辑代码示例已修复。
回答by Xenph Yan
SELECT 'DROP TABLE "' + TABLE_NAME + '"'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '[prefix]%'
This will generate a script.
这将生成一个脚本。
Adding clause to check existence of table before deleting:
添加子句以在删除前检查表是否存在：
SELECT 'IF OBJECT_ID(''' +TABLE_NAME + ''') IS NOT NULL BEGIN DROP TABLE [' + TABLE_NAME + '] END;'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '[prefix]%'
回答by Tony O'Hagan
This will get you the tables in foreign key order and avoid dropping some of the tables created by SQL Server. The t.Ordinalvalue will slice the tables into dependency layers.
这将为您提供外键顺序的表，并避免删除 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
回答by Rosdi Kasim
On Oracle XE this works:
在 Oracle XE 上，这有效：
SELECT 'DROP TABLE "' || TABLE_NAME || '";'
FROM USER_TABLES
WHERE TABLE_NAME LIKE 'YOURTABLEPREFIX%'
Or if you want to remove the constraints and free up spaceas well, use this:
或者，如果您想删除约束并释放空间，请使用以下命令：
SELECT 'DROP TABLE "' || TABLE_NAME || '" cascade constraints PURGE;'
FROM USER_TABLES
WHERE TABLE_NAME LIKE 'YOURTABLEPREFIX%'
Which will generate a bunch of DROP TABLE cascade constraints PURGEstatements...
这将生成一堆DROP TABLE cascade constraints PURGE语句......
For VIEWSuse this:
对于VIEWS使用此：
SELECT 'DROP VIEW "' || VIEW_NAME || '";'
FROM USER_VIEWS
WHERE VIEW_NAME LIKE 'YOURVIEWPREFIX%'
回答by vencedor
Here is my solution:
这是我的解决方案：
SELECT CONCAT('DROP TABLE `', TABLE_NAME,'`;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'TABLE_PREFIX_GOES_HERE%';
And of course you need to replace TABLE_PREFIX_GOES_HEREwith your prefix.
当然你需要TABLE_PREFIX_GOES_HERE用你的前缀替换。
回答by mrosiak
EXEC sp_MSforeachtable 'if PARSENAME("?",1) like ''%CertainString%'' DROP TABLE ?'
Edit:
编辑：
sp_MSforeachtable is undocumented hence not suitable for production because it's behavior may vary depending on MS_SQL version.
sp_MSforeachtable 没有记录，因此不适合生产，因为它的行为可能因 MS_SQL 版本而异。
回答by talsibony
I saw this post when I was looking for mysql statement to drop all WordPress tables based on @Xenph Yan here is what I did eventually:
我在寻找 mysql 语句以删除基于 @Xenph Yan 的所有 WordPress 表时看到了这篇文章，这是我最终做的：
SELECT CONCAT( 'DROP TABLE `', TABLE_NAME, '`;' ) AS query
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'wp_%'
this will give you the set of drop queries for all tables begins with wp_
这将为您提供一组以 wp_ 开头的所有表的删除查询
回答by Cade Roux
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
回答by FryHard
Xenph Yan's answer was far cleaner than mine but here is mine all the same.
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
Just change tableNameto the characters that you want to search with.
只需更改tableName为您要搜索的字符即可。
回答by ASH
This worked for me.
这对我有用。
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;
