Cancellare tutte le Tabelle da un Database SQL Server

Mentre facevo delle prove con il mio Webmatrix, server aruba e database SQL avevo bisogno di cancellare  tutto il contenuto di quest ultimo, ma proprio tutto, Tabelle, Stored Procedure, Funzioni, Viste, Relazionie Chiavi Primarie. Ho scoperto con stupore che non avevo i permessi per eliminare tutto il database dal server SQL e ricrearlo da capo e chiamarlo come volevo quindi ho trovato il seguente script che si può usare per eliminare tutto il contenuto.

una volta lanciato questa routine il database sarà completamente vuoto, come se fosse stato appena creato.

/* 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

/* 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

/* 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

codice di http://nicolaiarocci.com/

questa routine accede alla tabella di sistema sys.objects per reperire lo schema del database. la tecnica di accesso alle informazioni 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.

Nota Bene

Probabilmente c’è una ottima ragione per cui non disponete dei diritti di cancellazione del database.

Prima di lanciare questo codice chiedetevi 

1. se state sul database giusto, 
2. se siete coscienti delle conseguenze di quel che state per fare e 
3. se disponete di un backup dei dati. In ogni caso se perdete dati preziosi.

per altre info chiedete tramite il form mail che trovate su http://www.prcant.net