Nello script eseguo un controllo, tramite la funzione scalare IDENT_CURRENT, per poter chiamare (per inizializzare nella maniera corretta eventuali campi identity) il comando DBCC CHECKIDENT con 0 oppure con 1.
Questa funzione ha però un piccolo, subdolo, bug.
La funzione scalare IDENT_CURRENT ritorna 1 per una colonna IDENTITY(1,1) sia che non ci siano righe nella tabella, sia che ci sia una sola righa con IDENTITY = 1.
Il problema, quindi, è "solo" questo: non siamo in grado di stabilire se la tabella non sia mai stata utilizzata oppure se conteneva una sola riga con identity = 1.
Analogamente non esiste un modo per conoscere quali sono le tabelle “vergini” (mai utilizzate).
/* Memorizzo le tabelle che hanno una sola riga al loro interno */
select tableName into #dbcc from #results where NrRows = 1
set @tsql = ' DBCC CHECKIDENT (' + @tName + ', RESEED, 0) '
/*
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
if object_id('tempdb..#dbcc') is not null
drop table #dbcc
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
/* Memorizzo le tabelle che hanno una sola riga al loro interno */
select tableName into #dbcc from #results where NrRows = 1
/* 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
/*
elimino le tabelle sulle quali chiamerò la DBCC CHECKIDENT successivamente
*/
delete from #results
where tableName IN (select tableName from #dbcc)
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 'Forced DBCC'
select top 1 @tName = tableName from #dbcc
while exists (select 1 from #dbcc)
begin
set @tsql = ' DBCC CHECKIDENT (' + @tName + ', RESEED, 0) '
print @tsql
begin try
exec sp_executesql @tsql
end try
begin catch
end catch
delete from #dbcc where tableName = @tName
select top 1 @tName = tableName from #dbcc
end
print '*** end ***'