I recently found myself in a position whereby I needed to know how the identity columns within a given database where configured. The information I was hunting for was as follows:

1. Identity column name and table the column belonged to.
2. The column Data Type
3. The last seed value used.
4. The difference between the maximum value allowed for the Data Type and the current seed value.

I wanted something that gave a rough, first pass account of this detail that I could script in under ten minutes (so not a complete solution by any account). I thought I’d share the result, that can be taken and expanded upon as needed. The core part of this script relies on the sys.identity_columns view, which contains the essential detail behind an identity column such as the seed value, increment and last seed value used. As this view uses sys.columns as its backbone, you also get all of the standard column metadata included like the column name, information on which table the column relates to and the column Data Type to boot; bonus!

You’ll notice that the following code assumes a seed ‘increment_value’ of 1, but this is an enhancement that can be easily made. Here is the current SQL in its raw form:

-- A first stab at some operational monitoring. Grab all of the columns that support 'seeding' and, based on type, look at the last seed value recorded against
-- the maximum supported value (could expand this, use it for reporting, etc.)
SELECT 
st.[name] AS [TableName]
, ic.[Name] AS [SeedColumnName]
, stype.system_type_id AS [DataTypeId]
, stype.[name] AS [DataTypeName]
, 
CASE
	WHEN stype.system_type_id = 56
	THEN '2147483647'
	WHEN stype.system_type_id = 127
	THEN '9223372036854775807'
	ELSE 'Unknown'
END AS [MaximumAllowedPositiveValue]
, COALESCE(CONVERT(NVARCHAR(20), ic.last_value), 'Last Value Unset') AS [LastSeedValue]
, 
CASE
	WHEN COALESCE(CONVERT(NVARCHAR(20), ic.last_value), '') = ''
	THEN 'Cannot Calculate'
	WHEN stype.system_type_id = 56
	THEN CONVERT(NVARCHAR(20), (2147483647 - CAST(ic.last_value AS INT)))
	WHEN stype.system_type_id = 127
	THEN CONVERT(NVARCHAR(20), (9223372036854775807 - CAST(ic.last_value AS BIGINT)))
	ELSE 'Unknown'
END AS [CurrentDifferenceBetweenSeedAndMax]
FROM sys.identity_columns ic
	INNER JOIN sys.tables st ON ic.object_id = st.object_id	
	INNER JOIN sys.types stype ON ic.system_type_id = stype.system_type_id
ORDER by st.[Name];

You should get output a little something like this:

The ‘last_value’ column is of type sql_variant so note the use of casting where appropriate. I’ve observed that there is a possibility of the ‘last_value’ being NULL, so I’ve attempted to cater for this using a case statement (more robust code is most likely needed here). The use of NVARCHAR(20) for conversion is built with INT/BIGINT in mind, this can be adjusted for identity values that use Data Types outside of this range.

I hope this comes in handy and/or forms the basis for a more complete solution in the future, I’ll tinker with this in due course no doubt as I need to expand it.

Here’s me signing off until the next time.