Cancellare tabelle di un database

Cancellare tabelle di un database SQL Server 2019

In questo post vedremo insieme come cancellare tabelle di un database. L’aspetto interessante sarà vedere come eliminare le tabelle massivamente ed insieme allo script di cancellazione vedremo anche come cancellare le varie viste, Stored Procedure, VisteRelazioni e Chiavi Primarie.

Sicuramente, se hai conoscenza di Sql Server, la prima risposta utile per cancellare tabelle di un database è eseguire il comando DROP TABLE.

Cancellare tabelle di un database - Comando Drop table
Cancellare tabelle di un database – Comando Drop Table

La risposta è corretta ma se abbiamo la necessita di cancellare 100 tabelle? Oppure se non abbiamo i tutte le autorizzazioni necessarie?

Vediamo insieme come procedere.

Limitazioni

Purtroppo per eliminare le tabelle in un database occorre tener conto anche di alcune limitazione che la stessa Microsoft ci riporta:

  • Non è possibile eliminare una tabella a cui fa riferimento un vincolo FOREIGN KEY. È prima necessario eliminare il vincolo FOREIGN KEY o la tabella di riferimento. Se con la stessa istruzione DROP TABLE si eliminano sia la tabella di riferimento che la tabella che contiene la chiave primaria, è necessario indicare la tabella di riferimento per prima nell’elenco.
  • Con l’eliminazione di una tabella, le regole o i valori predefiniti della tabella vengono disassociati e i vincoli o trigger associati alla tabella vengono eliminati automaticamente. Se la tabella viene ricreata, è necessario associare nuovamente le regole e i valori predefiniti appropriati, ricreare eventuali trigger e aggiungere tutti i vincoli necessari.
  • Se si elimina una tabella che contiene una colonna varbinary (max) con l’attributo FILESTREAM, non verrà rimosso alcun dato archiviato nel file system.
  • DROP TABLE e CREATE TABLE non devono essere eseguiti nella stessa tabella nello stesso batch. In caso contrario, è possibile che si verifichi un errore imprevisto.
  • Le viste o stored procedure che fanno riferimento alla tabella eliminata devono essere eliminate o modificate in modo esplicito per eliminare il riferimento alla tabella.

Cancellare tabelle di un database: eliminiamo le Primary e Foreign Key

Prima di cancellare le tabelle di un database occorre dare una rapida occhiata alla struttura delle tabelle, mi riferisco alle Primary Key e Foreign Key.

Puoi farlo attraverso questo comodo script:

/* Cancella tutti i vincoli di Foreign Key */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT ' + RTRIM(@constraint)
        EXEC (@SQL)
        PRINT 'Cancallato Vincolo FK: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Cancella i vincoli Primary Key */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT ' + RTRIM(@constraint)
        EXEC (@SQL)
        PRINT 'Cancellato Vincolo PK: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

Cancellare tabelle di un database

Dopo aver rimosso le Primary Key e Foreign Key possiamo procedere nel cancellare tabelle di un database con il seguente codice:

/* Cancellare tabelle di un database - Drop di tutte le Tabelle */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Cancellata Tabella: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

Naturalmente sia lo script precedente che quanto riportato ora devono essere adattati e testati in base alla propria casistica.

Consiglio prima di procedere di eseguire una copia di backup del proprio database e di testare il tutto in un ambiente di test o staging.

Cancellare tabelle di un database: Viste, Function e Stored Procedure

La tecnica utilizza per cancellare tabelle di un database in questo caso non è quella di utilizzare un cursore ma di selezionare il primo record utile per poi inoltrarci in un ciclo di cancellazioni ripetute. Il ciclo termina quando non ci sono più record che soddisfano i parametri di ricerca. Per evitare problemi coi vincoli relazionali è importante cancellare prima le relazioni e solo dopo le tabelle.

Ora passiamo alle Viste, Function e Stored Procedure:

/* Drop di tutte le Stored Procedure che non siano di sistema */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Cancellata Procedura: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop di tutte le viste */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Cancellata Vista: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop di tutte le funzioni */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Cancellata la Funzione: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

Conclusione

Abbiamo visto insieme come cancellare tabelle di un database utilizzando dei script che implementano N ripetizione fin quando non vi è più la condizione che lo soddisfa.

Se hai a disposizione altri script che svolgono la stessa funzione ma con logiche diverse puoi condividerlo nella sezione commenti.

Autore Bartolomeo

Consulente tecnico con la passione per il web e la tecnologia e tutto quello che le ronza intorno.
Nel tempo libero scrivo articoli per il mio blog su argomenti vari, in particolare su configurazione Windows, Linux e WordPress.

Condividi questo articolo!

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.

Ho letto la policy privacy e accetto il trattamento dei miei dati personali in conformità al D.Lgs. 196/2003