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.
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
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.
How to find missing Identity/Sequence numbers using T-SQL
Check out our other posts on Data Analysis / Data Validation / Data Cleansing
- How to Search in all Columns for all tables in a database for Date Value in SQL Server
- How to Find Percentage of Null Values in every Column of all the Tables in SQL Server Database
- How to Find Percentage of Empty or Blank Values in every Column of all the Tables in SQL Server Database
- How to Get Row Count Of All The Tables In SQL Server Database
- How to search for a String in all Columns in all tables in SQL Server Database
- How to find and Replace Text in all the columns in all the tables in SQL server Database
- How to find values with Leading or Trailing Spaces in SQL Server Table
- How to Trim Leading and Trailing spaces for all the values in all the Columns in all the Tables in a SQL Server Database
- How to Find Duplicate Records in All the Tables in SQL Server Database
- How to Find & Delete Duplicate Records on All Tables in SQL Server Database
- How to Generate Select all Columns with or Without Top X Rows From All Tables Query Dynamically in SQL Server
- Concatenate SQL Server Columns for each Table Query Dynamically in SQL Server
- How to Find Max and Min Values for all the columns for all the tables in SQL Server Database
No comments:
Post a Comment