Home > Scripts > Svuotare un database: un meccanismo automatico

Svuotare un database: un meccanismo automatico

Può capitare, per diversi motivi, di avere la necessità di svuotare un database per riportarlo allo stato "iniziale".

Per stato "iniziale" intendo il momento dell’installazione: nessuna riga (se non in eventuali tabelle che devono essere già popolate) e con i contatori delle colonne autoincrementanti a zero.

 


 

/*
Meccanismo di delete automatico
*/
set nocount on
print ‘*** delete’

declare @tName nvarchar(100)
declare @tSQL nvarchar(2000)
declare @nrRows bigint
declare @rowCount bigint

if object_id(‘tempdb..#temp’) is not null
drop table #temp
if object_id(‘tempdb..#results’) is not null
drop table #results

create table #results
(
  tableName varchar(50),
  nrRows bigint,
  reseed int
)

/* Recupero la lista delle tabelle del mio db */
select QUOTENAME(table_schema) + ‘.’ + QUOTENAME(table_name) as table_name
into #temp
from information_schema.tables
where table_type = ‘base table’

/* Recupero il numero di righe presenti in ciascuna tabella */
while exists (select 1 from #temp)
begin
  select top 1 @tName = table_name from #temp
  set @tSQL = ‘select ”’ + @tName + ”’ as TableName, count(*) as NrRows, 0 from ‘ + @tName
  print @tSQL
  insert into #results exec sp_executesql @tSQL
  delete from #temp where table_name = @tName
end

/* verifico se ho delle righe nel db */
select @nrRows = sum(nrRows) from #results
select @nrRows as nrRigheIniziali

/* se voglio vedere l’elenco delle tabelle con il numero di righe */
select * from #results

/* ciclo finchè ho righe */
while (@nrRows > 0)
begin
    /* ciclo finchè ho tabelle su cui eseguire l’operazione di delete */
    while exists (select 1 from #results)
    begin
        /* costruisco l’operazione di delete */
        select top 1 @tName = TableName, @nrRows = NrRows from #results
   
        if (@nrRows > 0)
        begin
            set @tSQL = ‘delete from ‘ + @tName

            print @tSQL
            begin try
                exec sp_executesql @tSQL

                set @rowCount = @@rowcount
                print ‘@rowCount: ‘ + cast(@rowCount as varchar(10))
            end try
            begin catch
                print error_message()
            end catch
        end
        else
        begin
            print ‘tabella ‘ + @tName + ‘ risulta vuota’
        end
        print ”

        /* elimino dal mio contenitore temporaneo la tabella su cui ho eseguito la delete */
        delete from #results where tableName = @tName
    end

    /* svuoto le mie tabelle temporanee */
    truncate table #temp
    truncate table #results
   
    /* riprendo l’elenco delle tabelle */
    insert #temp
 select QUOTENAME(table_schema) + ‘.’ + QUOTENAME(table_name) as table_name
 from information_schema.tables
 where table_type = ‘base table’

    /* ricalcolo il numero di righe di ciascuna tabella */
    while exists (select 1 from #temp)
    begin
      select top 1 @tName = table_name from #temp
      set @tSQL = ‘select ”’ + @tName + ”’ as TableName, count(*) as NrRows, 0 from ‘ + @tName
      insert into #results exec sp_executesql @tSQL
      delete from #temp where table_name = @tName
    end

    /* riconto il numero di righe totali del db */
    select @nrRows = sum(nrRows) from #results
end

/* (re)inizializzazione dei contatori */
print ‘*** reseed’

    /* svuoto le mie tabelle temporanee */
    truncate table #temp
    truncate table #results

    /* riprendo l’elenco delle tabelle */
    insert #temp
 select QUOTENAME(table_schema) + ‘.’ + QUOTENAME(table_name) as table_name
 from information_schema.tables
 where table_type = ‘base table’

    /*
    calcolo il numero di righe di ciascuna tabella ed eseguo l’operazione:
    IDENT_CURRENT(‘nomeTabella’) – IDENT_SEED(‘nomeTabella’)
    */
    while exists (select 1 from #temp)
    begin
        select top 1 @tName = table_name from #temp
        set @tSQL = ‘select ”’ + @tName + ”’ as TableName, count(*) as NrRows, IDENT_CURRENT(”’ + @tName + ”’) – IDENT_SEED(”’ + @tName + ”’) from ‘ + @tName
        insert into #results exec sp_executesql @tSQL

        delete from #temp where table_name = @tName
    end

    /*
    elimino eventuali tabelle che:
    1. hanno righe
    2. non necessitano la reinizializzazione (non hanno identity)
    */
    delete from #results
    where
        nrRows <> 0 or
        reseed IS NULL

    declare @reseed int
    select top 1 @tName = tableName, @reseed = reseed from #results

    /*
    Se:
        IDENT_CURRENT(‘nomeTabella’) – IDENT_SEED(‘nomeTabella’) = 0
    allora:
        DBCC CHECKIDENT (‘nomeTabella’, RESEED, 1)
    altrimenti:
        DBCC CHECKIDENT (‘nomeTabella’, RESEED, 0)
    */
    while exists (select 1 from #results)
    begin
        if (@reseed = 0)
            set @tsql = ‘      DBCC CHECKIDENT (‘ + @tName + ‘, RESEED, 1) ‘
        else
            set @tsql = ‘      DBCC CHECKIDENT (‘ + @tName + ‘, RESEED, 0) ‘

        print @tsql
       
        begin try
            exec sp_executesql @tsql
        end try
        begin catch
        end catch

        delete from #results where tableName = @tName
        select top 1 @tName = tableName, @reseed = reseed from #results
    end

print ‘*** end ***’

Chi è Davide Mauri

Microsoft Data Platform MVP dal 2007, Davide Mauri si occupa di Data Architecture e Big Data nel mondo dell'IoT. Attualmente ricopre il ruolo di "Director Software Development & Cloud Infrastructure" in Sensoria, societa specializzata nella creazione di Wearables e sensori per l'IoT applicati a tessuti ed oggetti sportivi.

Leggi Anche

Aggiornamento statistiche SQL per database che contengono External Table PolyBase

Recentemente, ho avuto l’occasione di configurare un piano di manutenzione database per un DB in …

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

sixty three − fifty five =

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.