SQL Server: Tips and Tricks - 2 (2017)

How to List all Missing Identity Values for all Tables in SQL Server Database

Scenario:

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.

Solution:

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

go

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

)

DECLARE cur CURSOR FOR

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

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @SQL VARCHAR(max)=NULL

DECLARE @InitialIDValue INT=1

DECLARE @MaxIdentityId INT

DECLARE @MAXID TABLE

(

maxidentityid INT

)

DELETE FROM @MAXID

DECLARE @SQLIdentity NVARCHAR(max)

SET @SQLIdentity='SELECT MAX(' + @IdentyColumnName + ') From ['

+ @SchemaName + '].[' + @TableName + ']'

INSERT INTO @MAXID

EXEC(@SQLIdentity)

--Select * from @MAXID

SET @MaxIdentityId=(SELECT *

FROM @MAXID)

PRINT @MaxIdentityId

WHILE @InitialIDValue <= @MaxIdentityId

BEGIN

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

END

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

END

CLOSE cur

DEALLOCATE cur

SELECT Db_name() AS DatabaseName,

schemaname,

tablename,

identitycolumname,

identitymissingvalue

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.

https://1.bp.blogspot.com/-sFhRJwdV850/VuhnUJsc3fI/AAAAAAAAG54/pzUWeaQCl4QoC8BQ7BlZVALBX6I-XPdNg/s640/Capture.PNG