Hello

You script is using compatibility views. I prefer using dedicated views even if I need to have several version for different SQL Server version.

Here is my SQL script for SQL Server 2005-2008 which source is from this site

http://vyaskn.tripod.com/sql_server_check_identity_columns.htm

and that I have modified a little to manage 'numeric' and 'decimal' and use directly "sys.identity_columns" system views dedicated for this purpose

Regards

Pascal

--

-- liste les colonnes IDENTITY avec leur max current value et le taux d'usage en fonction du type choisi

--

SELECT

QUOTENAME(SCHEMA_NAME(tab.[schema_id])) + '.' + QUOTENAME(tab.[name]) AS [TableName],

col.[name] AS [ColumnName],

typ.[name] AS [DataType],

col.[last_value] AS [CurrentIdentityValue],

col.[precision] AS [PrecisionValue],

CAST(ROUND(CASE col.[system_type_id]

WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(tab.[schema_id]) + '.' + tab.[name]) * 100.) / 9223372036854775807

WHEN 106 THEN (IDENT_CURRENT(SCHEMA_NAME(tab.[schema_id]) + '.' + tab.[name]) * 100.) / CONVERT(NUMERIC(38,0),REPLICATE(9,col.[precision]))

WHEN 108 THEN (IDENT_CURRENT(SCHEMA_NAME(tab.[schema_id]) + '.' + tab.[name]) * 100.) / CONVERT(NUMERIC(38,0),REPLICATE(9,col.[precision]))

WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(tab.[schema_id]) + '.' + tab.[name]) * 100.) / 2147483647

WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(tab.[schema_id]) + '.' + tab.[name]) * 100.) / 32767

WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(tab.[schema_id]) + '.' + tab.[name]) * 100.) / 255

END,2) as NUMERIC(4,2)) AS [PercentageUsed]

FROM

sys.identity_columns AS col

INNER JOIN sys.tables AS tab ON tab.[object_id] = col.[object_id]

INNER JOIN sys.types AS typ ON col.[system_type_id] = typ.[system_type_id]

WHERE

col.[is_identity] = 1

ORDER BY

[PercentageUsed] DESC