Удалить все таблицы, имена которых начинаются с определенной строки

Как я могу удалить все таблицы, имена которых начинаются с данной строки?

Я думаю, что это можно сделать с помощью некоторого динамического SQL и INFORMATION_SCHEMAтаблиц.

7.08.2008 04:41:37
14 ОТВЕТОВ
РЕШЕНИЕ

Возможно, вам придется изменить запрос, включив в него владельца, если в базе данных их несколько.

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

Это чище, чем использование двухэтапного подхода генерации скрипта и запуска. Но одним из преимуществ генерации скриптов является то, что они дают вам возможность просмотреть все, что будет запущено, до того, как он будет запущен.

Я знаю, что если бы я собирался сделать это с производственной базой данных, я был бы максимально осторожен.

Исправлен пример редактирования кода.

149
12.12.2016 09:00:06
Возможно, вам придется запускать этот сценарий несколько раз из-за ограничений внешнего ключа между основной и подробной таблицами.
Alexander Prokofyev 26.12.2008 05:24:04
В SQL Server 2005 мне пришлось изменить последние две строки на close cmds; deallocate cmds.
Hamish Grubijan 26.04.2011 14:08:14
Предупреждение : это решение также может удалять таблицы, созданные SQL Server! Мое решение ниже избегает этого и удаляет таблицы в порядке зависимости внешнего ключа.
Tony O'Hagan 19.07.2015 01:15:09
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]%'
115
6.07.2017 16:13:20
Я мог бы добавить, чтобы убрать скобки при замене префикса целевым префиксом.
Levitikon 20.05.2012 15:31:54
MYSQL: SELECT concat ('DROP TABLE', TABLE_NAME, ";") в качестве данных FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '[prefix]%' --- для тех, кто любит меня, нашел эту тему
Andre 6.11.2012 00:27:01
Результат содержит также просмотры
Ondra 29.03.2013 13:01:44
Не забудьте экранировать _, если это часть вашего префикса, например. WHERE TABLE_NAME LIKE 'em\_%' ESCAPE '\';
EM0 29.09.2015 14:37:53
Это создает сценарий, но как выполнить сценарий?
daOnlyBG 4.08.2017 20:45:19

Ответ Зенфа Яна был намного чище, чем мой, но все равно мой.

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на символы, которые вы хотите найти.

2
23.05.2017 12:18:17
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
4
13.09.2011 02:09:04
select 'DROP TABLE ' + name from sysobjects
where type = 'U' and sysobjects.name like '%test%'

- Test - имя таблицы

0
24.06.2013 17:59:54
это на самом деле ничего не выполняет, просто возвращает кучу команд.
Stealth Rabbi 12.03.2014 16:57:07

Это позволит вам получить таблицы в порядке внешнего ключа и избежать отбрасывания некоторых таблиц, созданных 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
16
2.12.2016 06:51:42
Tony O'Hagan 10.02.2014 08:08:52
Быстрое исправление: TableName появляется несколько раз в предложениях WHERE и должно быть заменено на OBJECT_NAME (so.object_id). Хороший сценарий!
witttness 23.09.2014 03:18:24
SELECT 'if object_id(''' + TABLE_NAME + ''') is not null begin drop table "' + TABLE_NAME + '" end;' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '[prefix]%'
0
1.08.2014 14:16:29

Я подозреваю, что мне пришлось сделать небольшой вывод из ответа Зенфа Яна, потому что у меня были таблицы, не входящие в схему по умолчанию.

SELECT 'DROP TABLE Databasename.schema.' + TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'strmatch%'
0
16.02.2015 01:05:02

На 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%'
6
2.12.2016 02:53:10
Работал отлично. Необходимо удалить 61 037 пустых таблиц из базы данных, используемой для обеспечения качества. Я использовал пример каскадных ограничений. Сгенерировал вывод, затем скопировал все это в скрипт и запустил его. Взял навсегда, но это сработало как шарм! Спасибо!
tehbeardedone 30.10.2019 16:52:31

Я увидел этот пост, когда искал оператор mysql для удаления всех таблиц WordPress, основанный на @Xenph Ян, вот что я в итоге сделал:

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

это даст вам набор запросов на удаление для всех таблиц, начинающихся с wp_

4
28.07.2015 06:14:25

Вот мое решение:

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

И, конечно же, вам нужно заменить TABLE_PREFIX_GOES_HEREна ваш префикс.

5
24.03.2016 13:40:15
EXEC sp_MSforeachtable 'if PARSENAME("?",1) like ''%CertainString%'' DROP TABLE ?'

Редактировать:

sp_MSforeachtable недокументирован, поэтому не подходит для работы, поскольку его поведение может варьироваться в зависимости от версии MS_SQL.

5
6.03.2018 09:28:59
Удивительный один вкладыш! За это следует проголосовать наверх.
user3413723 20.12.2017 17:17:04

В случае временных таблиц, вы можете попробовать

SELECT 'DROP TABLE "' + t.name + '"' 
FROM tempdb.sys.tables t
WHERE t.name LIKE '[prefix]%'
0
6.09.2016 11:21:10

Это сработало для меня.

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
15.08.2019 14:47:04