Recentemente ho avuto la necessità di dover effettuare il reseed a 1 dei valori di tutte le colonne Identity delle tabelle vuote di un database.
Scomponendo la richiesta in task più piccoli si sono individuati i seguenti due task:
- Individuare le tabelle senza record nel database
- Effettuare il reseed dell’unica colonna IDENTITY generando uno script con i comandi incapsulando il codice all’interno di una stored procedure
Per individuare le tabelle prive di righe può essere utilizzata la CTE spaceused sviluppata nell’articolo:
La CTE spaceused è stata ampliata ed è diventata una CTE multipla che contiene la query zerospaceused sulla quale si basa la outer-query che genera i comandi DBCC CHECKIDENT con l’opzione RESEED in grado di resettare il valore della colonna IDENTITY di una tabella. Per testare il funzionamento dello script viene utilizzato il database di esempio EmptyDB che contiene le tabelle dbo.Persons e dbo.Addresses, vuote ma che hanno contenuto record in un determinato momento.
Il seguente script T-SQL genera il database di esempio EmptyDB.
USE [master];
GO
-- Drop Database
IF (DB_ID('EmptyDB') IS NOT NULL)
BEGIN
ALTER DATABASE [EmptyDB]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [EmptyDB];
END;
GO
USE [master];
GO
CREATE DATABASE [EmptyDB]
ON PRIMARY
(
NAME = N'EmptyDB'
,FILENAME = N'C:\SQL\DBs\EmptyDB.mdf'
,SIZE = 8192KB
,FILEGROWTH = 65536KB
)
LOG ON
(
NAME = N'EmptyDB_log'
,FILENAME = N'C:\SQL\DBs\EmptyDB_log.ldf'
,SIZE = 8192KB
,FILEGROWTH = 65536KB
)
WITH LEDGER = OFF
GO
USE [EmptyDB];
CREATE TABLE dbo.Persons
(
ID INTEGER IDENTITY(1, 1) NOT NULL
,FirstName NVARCHAR(64) NOT NULL
,LastName NVARCHAR(64) NOT NULL
);
GO
CREATE TABLE dbo.Addresses
(
ID INTEGER IDENTITY(1, 1) NOT NULL
,Address1 NVARCHAR(128) NOT NULL
,Address2 NVARCHAR(128) NOT NULL
);
GO
INSERT INTO dbo.Persons (FirstName, LastName) VALUES ('Rob', 'Walters'), ('Gail', 'Erickson');
GO
DELETE FROM dbo.Persons;
GO
INSERT INTO dbo.Addresses (Address1, Address2) VALUES ('1970 Napa Ct.', '6387 Scenic Avenue');
GO
DELETE FROM dbo.Addresses;
GO
Il seguente script T-SQL allestisce i comandi DBCC CHECKIDENT nella variabile locale @cmd. Se viene eseguito con una connessione al database EmptyDB genererà i comandi T-SQL per il reseed delle tabelle dbo.Persons e dbo.Addresses.
USE [EmptyDB];
GO
DECLARE
@cmd NVARCHAR(MAX) = '';
WITH spaceused AS
(
SELECT
sys.dm_db_partition_stats.object_id
,reservedpages = SUM(reserved_page_count)
,it_reservedpages = SUM(ISNULL(its.it_reserved_page_count, 0))
,usedpages = SUM(used_page_count)
,it_usedpages = SUM(ISNULL(its.it_used_page_count, 0))
,pages = SUM(CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
)
,row_Count = SUM(CASE WHEN (index_id < 2) THEN row_count ELSE 0 END)
FROM
sys.dm_db_partition_stats
JOIN
sys.objects ON sys.objects.object_id=sys.dm_db_partition_stats.object_id
OUTER APPLY
(SELECT
reserved_page_count AS it_reserved_page_count
,used_page_count AS it_used_page_count
FROM
sys.internal_tables AS it
WHERE
it.parent_id = object_id
AND it.internal_type IN (202, 204, 211, 212, 213, 214, 215, 216)
AND object_id = it.object_id
) AS its
WHERE
sys.objects.type IN ('U', 'V')
GROUP BY
sys.dm_db_partition_stats.object_id
), zerospaceused
AS
(
SELECT
table_name = OBJECT_NAME ([object_id])
,table_schema = OBJECT_SCHEMA_NAME ([object_id])
,rows = convert (char(11), row_Count)
,reserved = LTRIM (STR (reservedpages * 8, 15, 0) + ' KB')
,it_reserved = LTRIM (STR (it_reservedpages * 8, 15, 0) + ' KB')
,tot_reserved = LTRIM (STR ( (reservedpages + it_reservedpages) * 8, 15, 0) + ' KB')
,data = LTRIM (STR (pages * 8, 15, 0) + ' KB')
,data_MB = LTRIM (STR ((pages * 8) / 1000.0, 15, 0) + ' MB')
,index_size = LTRIM (STR ((CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
,it_index_size = LTRIM (STR ((CASE WHEN it_usedpages > pages THEN (it_usedpages - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
,tot_index_size = LTRIM (STR ((CASE WHEN (usedpages + it_usedpages) > pages THEN ((usedpages + it_usedpages) - pages) ELSE 0 END) * 8, 15, 0) + ' KB')
,unused = LTRIM (STR ((CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')
FROM
spaceused
WHERE
row_Count = 0
)
SELECT
@cmd = @cmd + 'DBCC CHECKIDENT (''' + table_schema + '.' + table_name + ''', RESEED, 1);' + CHAR(13) + CHAR(10)
FROM
zerospaceused
;
PRINT(@cmd);
GO
L’ouput generato è il seguente:
DBCC CHECKIDENT ('dbo.Persons', RESEED, 1);
DBCC CHECKIDENT ('dbo.Addresses', RESEED, 1);
Sarà quindi possibile eseguire i comandi modificando l’azione da compiere con il contenuto della variabile @cmd. PRINT(@cmd) visualizza i comandi, EXEC(@cmd) li applica.
Buon divertimento!