如何删除名称以给定字符串开头的所有表?
我认为这可以通过一些动态SQL和INFORMATION_SCHEMA
表来实现。
发布于 2008-08-07 04:44:48
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]%'
发布于 2014-02-10 16:04:44
这将使您按外键顺序获得表,并避免删除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
发布于 2016-03-24 20:19:15
以下是我的解决方案:
SELECT CONCAT('DROP TABLE `', TABLE_NAME,'`;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'TABLE_PREFIX_GOES_HERE%';
当然,您需要将TABLE_PREFIX_GOES_HERE
替换为您的前缀。
https://stackoverflow.com/questions/4393
复制相似问题