Monitoring identity columns

IDENT_CURRENT is your friend :)

Edit -> Wow, that was really embarrasing. I wrote a proc a few years back to gather those stats, and it was huge. Now it's eminently simple. I've just written it here as a multi-statement function, but it could just as easily be an in-line function.

I really hope no-one saw this post before I edited it!!

CREATE FUNCTION [dbo].[fn_GetIdentityColumnValues]()
  RETURNS @Results TABLE (TableName sysname, IdentityColumnName sysname, CurrentIdentityValue [numeric](38,0))
AS
BEGIN

INSERT INTO @Results (TableName, IdentityColumnName, CurrentIdentityValue)
SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.' + QUOTENAME(OBJECT_NAME([object_id])), 
       QUOTENAME([name]), 
       IDENT_CURRENT(QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.' + QUOTENAME(OBJECT_NAME([object_id]))) FROM [sys].[identity_columns]

RETURN
END

Ok, here's another one (just a plain SELECT), that gives you the maximum value for integer and numeric / decimal columns. Note that it doesn't go all the way up to the limit on numeric and decimal columns, because POWER() epic fails when raising 10 to the power of 29 or more, and gets the wrong value on 10 ^ 28.

SELECT [table_name], [identity_column_name], [next_value], [max_value], [max_value] - [next_value] AS [values_left] FROM (
    SELECT QUOTENAME(OBJECT_SCHEMA_NAME([ic].[object_id])) + '.' + QUOTENAME(OBJECT_NAME([ic].[object_id])) AS table_name,
           QUOTENAME([ic].[name]) AS identity_column_name,
           ISNULL(CONVERT(NUMERIC (38, 0), [ic].[last_value]) + CONVERT(NUMERIC (38, 0), [ic].[increment_value]), 
                  CONVERT(NUMERIC (38, 0), [ic].[seed_value]) + CONVERT(NUMERIC (38, 0), [ic].[increment_value])) AS next_value,
           CASE WHEN system_type_id IN (48, 52, 56, 127) THEN POWER(CONVERT(NUMERIC(38, 0), 2), (max_length * 8) - 1) - 1 
                WHEN system_type_id IN (106, 108) THEN POWER(CONVERT(NUMERIC(38, 0), 10), CASE WHEN [precision] - [scale] < 29 THEN ([precision] - [scale]) - 1 ELSE 27 END) - 1 ELSE 0 END AS max_value
    FROM   [sys].[identity_columns] AS [ic]
           INNER JOIN
           [sys].[objects] AS [o]
           ON [o].[object_id] = [ic].[object_id]
    WHERE  [o].[type] = 'U'
) tableData