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

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:

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!

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

SQL Start 2024 – Save the date!

Focalizzato sulla Data Platform di Microsoft e sulle tecnologie ad esso correlate, anche quest’anno si …