Svuotare un database: un meccanismo automatico

 

---

 

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