Script to check current identity of Tables

  • Goofy

    SSC Journeyman

    Points: 97

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

  • WILLIAM MITCHELL

    SSChampion

    Points: 13697

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

    WHERE IDENT_CURRENT(t.name) IS NOT NULL

    ORDER BY 1

  • pmonschein

    SSC Veteran

    Points: 202

    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

  • WILLIAM MITCHELL

    SSChampion

    Points: 13697

    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]

  • pmonschein

    SSC Veteran

    Points: 202

    Oh yes ! you're right, thanks !

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

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

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