InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 18:03:10 GMT20http://www.sqlservercentral.com/Forums/Topic878379-2636-1.aspxOh yes ! you're right, thanks !Fri, 19 Mar 2010 09:51:20 GMTpmonscheinhttp://www.sqlservercentral.com/Forums/Topic878379-2636-1.aspxIf any of your tables are at approx 99.995% or higher, the script errors out with:Msg 8115, Level 16, State 8, Line 1Arithmetic overflow error converting numeric to data type numeric....so you need to change as NUMERIC(4,2)) AS [PercentageUsed] toas NUMERIC(5,2)) AS [PercentageUsed] Thu, 18 Mar 2010 19:30:45 GMTWILLIAM MITCHELLhttp://www.sqlservercentral.com/Forums/Topic878379-2636-1.aspxHelloYou 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 [url]http://vyaskn.tripod.com/sql_server_check_identity_columns.htm[/url]and that I have modified a little to manage 'numeric' and 'decimal' and use directly "sys.identity_columns" system views dedicated for this purposeRegardsPascal[code="sql"]---- 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] = 1ORDER BY [PercentageUsed] DESC[/code]Thu, 18 Mar 2010 03:49:14 GMTpmonscheinhttp://www.sqlservercentral.com/Forums/Topic878379-2636-1.aspx[code="sql"]SELECT t.name, IDENT_CURRENT(t.name) FROM sys.tables tWHERE IDENT_CURRENT(t.name) IS NOT NULLORDER BY 1[/code]Wed, 17 Mar 2010 05:51:08 GMTWILLIAM MITCHELLhttp://www.sqlservercentral.com/Forums/Topic878379-2636-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/IDENTITY/69736/">Script to check current identity of Tables</A>[/B]Sun, 07 Mar 2010 18:29:00 GMTGoofy