SQL - Surveiller un compteur d'identité

Sur les tables SQL Server mettre un compteur d'identité comme clé primaire, c'est bien. (Ça résous pas mal de problématiques).
Mais quand la base a une forte (très forte) activité et que l'on utilise des colonnes de type INT pour ces compteurs, ben des fois on arrive au bout du compteur !!!
A ce moment la c'est trop tard votre application va subir des bugs à répétition jusqu’à ce que vous solutionniez ce problème. Autant dire qu'il faut réagir vite et sous la pression. C'est pas bon.

Pour éviter cela je propose de surveiller ces compteurs et de faire un reporting qui vous indique quand agir par anticipation.

Voici ci dessous une procédure qui stocke dans une table dbo.compteur_identite la valeur de l'auto-incrémenté et combien de lignes il reste à insérer sereinement.
En dessous de 1 Million vous avez un warning.

--- Vider la table si elle existe déjà
IF (EXISTS (SELECT 1 FROM sys.schemas s 
                     INNER JOIN sys.tables t ON t.schema_id = s.schema_id 
                     WHERE s.name = 'dbo' 
                       AND t.name = 'compteur_identite'))
BEGIN
 DROP TABLE dbo.compteur_identite
END 

--- recréer la table de sortie
CREATE TABLE dbo.compteur_identite ( [schema] VARCHAR(10) NOT NULL
, [table] VARCHAR(100) NOT NULL, [index_pk] VARCHAR(100) NOT NULL
, [column] VARCHAR(100) NOT NULL, [type] VARCHAR(25) NOT NULL
, [seed] BIGINT NULL, [increment] BIGINT  NULL
, [max_id] NUMERIC(38,0)  NULL, [current] NUMERIC(38,0) NULL
, [nombre de lignes restant] NUMERIC(38,0) NULL
, [critique] VARCHAR(10) NULL)


--- curseur pour parcourir toutes les tables de la base de données
DECLARE @schema VARCHAR(5);
DECLARE @table VARCHAR(100);
DECLARE curseur CURSOR FOR SELECT s.name, o.name 
                           FROM sys.schemas s 
                           INNER JOIN sys.objects o ON o.schema_id = s.schema_id 
                           WHERE s.name <> 'sys' 
                             AND o.name NOT IN ('MASTERKEY', 'spt_values')  --- ici exclure ce qui n'est pas nécessaire

OPEN curseur
FETCH NEXT FROM curseur INTO  @schema, @table
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
  PRINT @schema + '.' + @table;
  --- insertion dans la table de sortie
  INSERT INTO dbo.compteur_identite ( [schema]
    , [table], [index_pk]
    , [column], [type]
    , [seed], [increment]
    , [max_id], [current]
    , [nombre de lignes restant]
    , [critique])
  SELECT s.name AS [schema]
    , o.name AS [table], i.name AS [index_pk]
    , c.name AS [column], t.name AS [type]
    , IDENT_SEED(@schema + '.' + @table) AS [seed], IDENT_INCR(@schema + '.' + @table) AS [increment]
    , CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) AS [max_id], IDENT_CURRENT( @schema + '.' + @table) AS [current]
    , CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) - IDENT_CURRENT( @schema + '.' + @table ) AS [nombre de lignes restant]
    , CASE WHEN CONVERT(NUMERIC(38,0), POWER(2.0, 8.0 * c.max_length)-1) - IDENT_CURRENT( @schema + '.' + @table )  < 1000000 THEN 'Warning' ELSE 'Non' END AS [critique]
  FROM sys.indexes i 
  INNER JOIN sys.objects o ON i.object_id = o.object_id 
  INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
  INNER JOIN sys.index_columns cl ON cl.object_id = o.object_id AND i.index_id = cl.index_id
  INNER JOIN sys.columns c ON c.object_id = o.object_id AND cl.column_id = c.column_id AND c.is_identity = 1
  INNER JOIN sys.systypes t ON c.user_type_id = t.xusertype AND t.collation IS NULL AND t.length > 0 AND xprec <> 0
  WHERE i.type = 1  --- pk
    AND s.name = @schema 
    AND o.name = @table
  ;
  FETCH NEXT FROM curseur INTO  @schema, @table;
END
CLOSE curseur;
DEALLOCATE curseur;

--- Affichage du resultat du jour (Tri en fonction des besoins !)
SELECT * FROM dbo.compteur_identite
ORDER BY [schema], [table], [index_pk]
 ;

Bien sût cette requête doit être adaptée a vos besoins.

Quelques infos clé :

• Le nom de la table dbo.compteur_identite est arbitraire choisissez celui qui vous fait plaisir ;-)
• Dans la déclaration du curseur sur la liste des tables a surveiller retirer les tables inutiles, moi j'ai retiré le schéma sys et les deux tables 'MASTERKEY' et 'spt_values'. 
• Calcul de la colonne "Critique", j'ai posé un seuil à 1 million de lignes restant le seuil doit être ajusté en fonction de la vitesse de remplissage et de votre capacité à agir lorsqu'il n'y a plus assez de place.
• Les filtres sur les types de colonne sont assez obscur, je les ai déterminé par recherche : Tout ce qui a une collation c'est des colonnes de type texte : pas de problème ici. Tout les type avec une longeur à 0 ou négative (il y en an si si) sont filtré pour que les calculs restent cohérents, les type avec une xprec à 0 sont aussi filtrées (Exemple les type images).
• La requête finale est a ajusté en fonction de vos besoins et peut être jouée autant de fois que nécessaire.

Enjoy !