Let us assume that there is an Employee table with EmployeeId as the Identity column in which some values are missing.  The following batch will show all the missing values in that Identity column. The logic I preferred is to create another table with the same increment as that of the parent table but without any gaps. Finally, subtracting the parent table rows from the newly created table gives us the missed values. Rest of the script is pretty straight forward and self explanatory.

 Declare @MinEmpId int

Declare @MaxEmpId int

Declare @sql nvarchar(250)

select @MinEmpId = Min(EmployeeID),@MaxEmpId = Max(EmployeeID) from Employee

SET @sql = 'Create Table Temp1(Id int IDENTITY(' + cast(@MinEmpId as varchar(25))+ ',1),DummyCol int)'

EXEC sp_executesql @sql

while(@MinEmpId <= @MaxEmpId)

begin

Insert into Temp1(DummyCol) Values(1)

set @MinEmpId = @MinEmpId + 1

end

Select Id from Temp1

Except

Select EmployeeId from Employee

Drop Table Temp1

The above script can be made reusable by making it a stored procedure using Dynamic Sql as shown below. It just accepts table name as a parameter and does the rest of the job.

CREATE PROC GetMissingIds(@TableName Varchar(50))

AS

BEGIN

Declare @MinEmpId int

Declare @MaxEmpId int

Declare @Increment int

Declare @sql nvarchar(250)

SET @sql = 'SELECT @Increment=IDENT_INCR(''' + @TableName + ''')'

EXEC sp_executesql @sql, N'@Increment Int OUTPUT', @Increment OUTPUT

SET @sql = 'select @MinEmpId=Min($Identity),@MaxEmpId=Max($Identity) from ' + @TableName

EXEC sp_executesql @sql, N'@MinEmpId Int OUTPUT,@MaxEmpId Int OUTPUT',@MinEmpId OUTPUT,@MaxEmpId OUTPUT

SET @sql = 'Create Table Temp1(Id int IDENTITY(' + cast(@MinEmpId as varchar(25))+ ',' + cast(@Increment as Varchar(10)) + '),DummyCol int)'

EXEC sp_executesql @sql

 

while(@MinEmpId <= @MaxEmpId)

begin

Insert into Temp1(DummyCol) Values(1)

set @MinEmpId = @MinEmpId + @Increment

end

SET @sql = 'Select Id from Temp1 EXCEPT Select $Identity From ' + @TableName

EXEC sp_executesql @sql

 

Drop Table Temp1

 

END

Hope this helps.