How to List all Missing Identity Values for all Tables in SQL Server Database - SQL Server / T-SQL Tutorial

Scenario : Download Script

You are working as SQL Server Developer / TSQL developer for Law firm. You are doing working on data validation / data analysis project. Once of the task you got for analysis, You need to list all the missing Identity values for all the tables in SQL Server Database. 


We will use cursor to loop through all the tables in a database which has identity column. we will load the missing identity values for each table in temp table and print at the end. We will get Database Name, Schema Name, Table Name, Identity Column and Missing Identity Values. 

If you would like to run the script for single table. You can edit Where clause and include Table Name.

USE YourDBName


DECLARE @SchemaName VARCHAR(100) 
DECLARE @TableName VARCHAR(100) 
DECLARE @DatabaseName VARCHAR(100) 
DECLARE @IdentyColumnName VARCHAR(100) 

--Create Temp Table to Save Results    
IF Object_id('tempdb..##Results') IS NOT NULL 
  DROP TABLE ##results 

CREATE TABLE ##results 
     schemaname           VARCHAR(100), 
     tablename            VARCHAR(100), 
     identitycolumname    VARCHAR(100), 
     identitymissingvalue INT 

  SELECT Schema_name(schema_id)   AS SchemaName, 
         Object_name(c.object_id) AS TableName, 
         C.NAME                   AS IdentityColumnName 
  FROM   sys.columns c 
         INNER JOIN sys.tables t 
                 ON c.object_id = t.object_id 
  WHERE  is_identity = 1 

OPEN cur 

FETCH next FROM cur INTO @SchemaName, @TableName, @IdentyColumnName 

      DECLARE @InitialIDValue INT=1 
      DECLARE @MaxIdentityId INT 
           maxidentityid INT 


      DECLARE @SQLIdentity NVARCHAR(max) 

      SET @SQLIdentity='SELECT MAX(' + @IdentyColumnName + ') From [' 
                       + @SchemaName + '].[' + @TableName + ']' 


      --Select * from @MAXID  
      SET @MaxIdentityId=(SELECT * 
                          FROM   @MAXID) 

      PRINT @MaxIdentityId 

      WHILE @InitialIDValue <= @MaxIdentityId 
            DECLARE @SQLQuery NVARCHAR(max)=NULL 
            DECLARE @InitialIDValueCHAR VARCHAR(10)=NULL 

            SET @InitialIDValueCHAR=Cast(@InitialIDValue AS VARCHAR(10)) 

            PRINT @InitialIDValueCHAR 

            SET @SQLQuery=' IF NOT EXISTS (SELECT 1 FROM [' 
                          + @SchemaName + '].[' + @TableName 
                          + ']                    WHERE [' 
                          + @IdentyColumnName + '] = ' 
                          + @InitialIDValueCHAR + ')            INSERT INTO ##results(SchemaName,TableName,IdentityColumName,IdentityMissingValue )            VALUES ( ''' 
                          + @SchemaName + '''' + ',''' + @TableName + '''' + 
                          + @IdentyColumnName + '''' + ',''' 
                          + @InitialIDValueCHAR + ''')' 

            PRINT @SQLQuery 

            EXEC (@SQLQuery) 

            SET @InitialIDValue = @InitialIDValue + 1 

      FETCH next FROM cur INTO @SchemaName, @TableName, @IdentyColumnName 

CLOSE cur 


SELECT Db_name() AS DatabaseName, 
FROM   ##results 
ORDER  BY tablename 
--drop table #Results 

I ran above query on my Database to Identity missing identity values for all the tables and got below results.
How to find missing Identity/Sequence numbers using T-SQL

Check out our other posts on Data Analysis / Data Validation / Data Cleansing