Script to find the current value of identity columns in the entire SQL Server database
Identity constraints plays a vital role in populating the data into the tables from application. This becomes more vital when we have heavy transaction oriented tables and that too participating in some sort of replication as part of high availability.
Identity constraints data population is also related to the data type of column.
For example, if there is any column having data type as 'int' then the maximum value for that column would be 2147483647. We have to keep looking at the value of that column so that it doesn't reach the max value and application doesn't starve.
Please create an alert of the below script and ensure to get the results periodically to monitor it.
SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName,
c.name AS ColumnName,
IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) AS CurrentIdentityValue,
IDENT_INCR (SCHEMA_NAME(t.schema_id) + '.' + t.name) as Identity_increment
FROM sys.columns AS c
INNER JOIN sys.tables AS t ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1
-- write here the table name if you want to filter for specific table
and t.name like '%TABLE_NAME%'
