Suppression de toutes les tables dont le nom commence par une certaine chaîne.

Question

Comment puis-je supprimer toutes les tables dont le nom commence par une chaîne donnée ?

Je pense que cela peut être fait avec un peu de SQL dynamique et le INFORMATION_SCHEMA tables.

Answer 1

Vous devrez peut-être modifier la requête pour inclure le propriétaire s'il y en a plusieurs dans la base de données.

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

C'est plus propre que d'utiliser une approche en deux étapes de génération de script plus exécution. Mais un avantage de la génération script est qu'elle vous donne la possibilité de revoir l'intégralité de ce qui va être exécuté avant qu'il ne le soit réellement.

Je sais que si je devais faire cela sur une base de données de production, je serais aussi prudent que possible.

Modifier Correction de l'échantillon de code.

Answer 2

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

Cela va générer un script.

Ajout d'une clause pour vérifier l'existence de la table avant de la supprimer :

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

Answer 3

Cela vous permettra d'obtenir les tables dans l'ordre des clés étrangères et d'éviter de supprimer certaines des tables créées par SQL Server. Le site t.Ordinal permettra de découper les tableaux en couches de dépendance.

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

Answer 4

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

Answer 5

Xenph Yan La réponse de l'auteur était bien plus claire que la mienne, mais voici la mienne quand même.

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

Changez juste tableName aux caractères que vous voulez rechercher.