Post reply

Script to check current identity of Tables

  • March 7, 2010 at 6:29 pm

    #212141

    Comments posted to this topic are about the item Script to check current identity of Tables

  • March 17, 2010 at 5:51 am

    #1135133

    SELECT t.name, IDENT_CURRENT(t.name) FROM sys.tables t

    WHERE IDENT_CURRENT(t.name) IS NOT NULL

    ORDER BY 1

  • March 18, 2010 at 3:49 am

    #1135798

    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

  • March 18, 2010 at 7:30 pm

    #1136405

    If any of your tables are at approx 99.995% or higher, the script errors out with:

    Msg 8115, Level 16, State 8, Line 1

    Arithmetic overflow error converting numeric to data type numeric.

    ...so you need to change

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

    to

    as NUMERIC(5,2)) AS [PercentageUsed]

  • March 19, 2010 at 9:51 am

    #1136849

    Oh yes ! you're right, thanks !

  • May 19, 2016 at 7:19 am

    #1880235

    Thanks for the script.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply