Come reimpostare i valori della colonna Identity per tutte le tabelle di un database SQL Server

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:

1. Individuare le tabelle senza record nel database
2. 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:

Qual è la tabella più grande nel DB corrente?

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!