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.
Ci sono diverse strade per poter fare questo:
- droppare il database e ricrearlo
- effettuare il restore del db iniziale, sovrascrivendo quello attuale
- ...
Può però anche esserci la necessità, per diversi motivi, di dover effettuare una pulizia via codice.
Abbiamo quindi a disposizione l'istruzione TRUNCATE e l'istruzione DELETE.
Non sto qui a ricordare le differenze sostanziali che passano tra le due istruzioni, mi limito solo a ricordare che in presenza di chiavi esterne non è possibile eseguire la prima delle due:
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'xyz' because it is being referenced by a FOREIGN KEY constraint.
Possiamo però costruire, ed è quello che farò nel post, un meccanismo di DELETE automatico che sia in grado di:
- cancellare le righe di tutte le tabelle che vogliamo svuotare (istruzione DELETE)
- riportare i contatori al loro stato iniziale (comando DBCC CHECKIDENT).
Due sono le note che voglio sottilineare, una per il primo ed una per il secondo punto.
Punto (1): in presenza di tabelle con relazioni eseguire una DELETE sulla tabella figlio, prima che sia svuotata la tabella padre, porta ad un errore: The DELETE statement conflicted with the REFERENCE constraint ...
Punto (2): sottolineo il comportamento che si ottiene eseguendo il comando DBCC su tabelle mai utilizzate, o su tabelle su cui sono stati fatti degli inserimenti, come spiegato perfettamente nei booksonline, quando eseguiamo il comando come:
DBCC CHECKIDENT (' + @tName + ', RESEED, 1)
Current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + 1.
Per entrambe le note esiste una soluzione:
- rieseguire le cancellazioni del db, così che la tabella figlia venga pulita
- controllare, tramite la funzione scalare IDENT_CURRENT, il valore dell'ultimo identity generato e comportarsi di conseguenza
Per realizzare il meccanismo automatico utilizzo un database molto semplice, costituito da tre tabelle, con un paio di relazioni.
Queste le istruzioni per costruirlo:
set nocount on
use master
go
if exists (select [name] from sys.databases WHERE name = N'identityTest')
drop database identityTest
go
create database identityTest
go
use identityTest
go
create table a
(
idRecord int primary key identity(1,1),
valore varchar(10)
)
go
create table b
(
idRecord int primary key identity(1,1),
valore varchar(10)
)
go
create table c
(
idRecord int primary key identity(1,1),
valore varchar(10),
idFKA int foreign key references a(idRecord),
idFKB int foreign key references b(idRecord)
)
go
Questo il diagramma del db costruito per i nostri scopi:
A questo punto inserisco alcuni dati di prova e vedo le tabelle:
/* inserisco righe nella tabella "a" e nella tabella "c" */
insert a values ('aaa')
insert a values ('aaa')
insert c values ('ccc', 1, NULL)
insert c values ('ccc', 1, NULL)
select * from a
select * from b
select * from c
Procediamo con la costruzione del meccanismo di cancellazione che sia in grado di:
- recuperare la lista delle tabelle del mio database (se devo escluderne alcune è sufficiente modificare la clausola WHERE di questa istruzione)
- contare, per ciascuna tabella, il numero di righe presenti (se = 0 è inutile che esegua la procedura di cancellazione)
- verificare se la tabella è mai stata utilizzata e, a seconda del risultato, eseguire:
- DBCC CHECKIDENT ('nomeTabella', RESEED, 1)
- DBCC CHECKIDENT ('nomeTabella', RESEED, 0)
Questo il codice TSQL, facilmente trasformabile in una stored procedure da poter chiamare nel momento in cui si presenti la necessità di svotare il database:
/*
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 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 + ']'
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 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 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 ***'