SQLServer: IDENTITYの上限値に近づいているテーブルを検知する方法

IDENTITYつきのカラムの値が上限に達すると、それ以上テーブルにINSERTできなくなります。

実験1. そもそも上限値を超えたSEEDを設定→エラー　（この場合、tinyintの上限値255に対してSEED256を設定しようとした）

実験2. 上限に達するとINSERTできない
まず上限ギリギリのSEED値を設定してから1レコードINSERT

次に、もう一行INSERTしようとするとエラーになり失敗

知らず知らずのうちにIDENTITY設定したカラムが上限値に達してINSERT不可能な状態に陥るのは避けたいため、一定の閾値（例えば上限値の8割）を超えたテーブルを検知する仕組みを作りたいと思い、調査しました。

特定のDBに対して、全テーブルのID値をチェックし、上限値の8割を超えたテーブルリストを抽出するクエリは以下のようになります。
※ID値が設定可能な tinyint / smallint/ int / bigint / decimal / numeric に対応しています。

declare @alert_rate float
set @alert_rate = 0.8

SELECT
    T.name as table_name,
    IDENT_CURRENT(T.name) as current_identity_value,
    C.name as column_name,
    Y.name as type_name,
    C.increment_value,
    C.precision
    ,IDENT_CURRENT(T.name) / (case
                                when Y.name = 'tinyint' then 255
                                when Y.name = 'smallint' then 32767
                                when Y.name = 'int' then 2147483647
                                when Y.name = 'bigint' then 9223372036854775807
                                when Y.name = 'decimal' then (POWER(CAST(10 AS float), C.precision - 1))
                                when Y.name = 'numeric' then (POWER(CAST(10 AS float), C.precision - 1))
                                else 1
                            end) as threshold_rate
FROM
     sys.tables AS T
JOIN sys.identity_columns AS C ON C.object_id = T.object_id
JOIN sys.types AS Y ON Y.system_type_id = C.system_type_id AND Y.user_type_id = C.user_type_id
WHERE
    T.type = 'U'
and IDENT_CURRENT(T.name) / (case
                                when Y.name = 'tinyint' then 255
                                when Y.name = 'smallint' then 32767
                                when Y.name = 'int' then 2147483647
                                when Y.name = 'bigint' then 9223372036854775807
                                when Y.name = 'decimal' then (POWER(CAST(10 AS float), C.precision - 1))
                                when Y.name = 'numeric' then (POWER(CAST(10 AS float), C.precision - 1))
                                else 1
                            end) > @alert_rate
ORDER BY
    T.name, C.column_id

これをサーバー内の各DB上で実行させるためにこちらのクエリと組み合わせると、以下のようなクエリとなります。

DECLARE
     @Database sysname
    ,@sql nvarchar(2000)

DECLARE
    CMyCursor CURSOR FAST_FORWARD FOR
        SELECT
            name
        FROM
            master.sys.databases WITH(NOLOCK)
        WHERE
        -- システムDBおよびディストリビューションDB除外、かつオンラインのDBに限定
            Cast(CASE WHEN name IN ('master', 'model', 'msdb', 'tempdb') THEN 1 ELSE is_distributor END As bit) = 0
        AND state = 0

OPEN CMyCursor
FETCH NEXT FROM CMyCursor INTO
    @Database

-- DB単位のループ
WHILE @@fetch_status = 0
BEGIN

    set @sql = ''
                        -- useを使う必要があるが、use単体でexecuteすると実行後にコンテキストが現在のDBに戻ってしまう。そのため丸ごと動的SQLで実行する
    set @sql = @sql + ' USE ' + CAST(@Database AS NVARCHAR(100)) + ';'
                        -- ここにDBごとに実行したい処理を書く
    set @sql = @sql + ' declare @alert_rate float '
    set @sql = @sql + ' set @alert_rate = 0.8 '

    set @sql = @sql + ' SELECT '
    set @sql = @sql + '     T.name as table_name, '
    set @sql = @sql + '     IDENT_CURRENT(T.name) as current_identity_value, '
    set @sql = @sql + '     C.name as column_name, '
    set @sql = @sql + '     Y.name as type_name, '
    set @sql = @sql + '     C.increment_value, '
    set @sql = @sql + '     C.precision '
    set @sql = @sql + '     ,IDENT_CURRENT(T.name) / (case '
    set @sql = @sql + '                                 when Y.name = ''tinyint'' then 255 '
    set @sql = @sql + '                                 when Y.name = ''smallint'' then 32767 '
    set @sql = @sql + '                                 when Y.name = ''int'' then 2147483647 '
    set @sql = @sql + '                                 when Y.name = ''bigint'' then 9223372036854775807 '
    set @sql = @sql + '                                 when Y.name = ''decimal'' then (POWER(CAST(10 AS float), C.precision - 1)) '
    set @sql = @sql + '                                 when Y.name = ''numeric'' then (POWER(CAST(10 AS float), C.precision - 1)) '
    set @sql = @sql + '                                 else 1 '
    set @sql = @sql + '                             end) as threshold_rate '
    set @sql = @sql + ' FROM '
    set @sql = @sql + '      sys.tables AS T '
    set @sql = @sql + ' JOIN sys.identity_columns AS C ON C.object_id = T.object_id '
    set @sql = @sql + ' JOIN sys.types AS Y ON Y.system_type_id = C.system_type_id AND Y.user_type_id = C.user_type_id '
    set @sql = @sql + ' WHERE '
    set @sql = @sql + '     T.type = ''U'' '
    set @sql = @sql + ' and IDENT_CURRENT(T.name) / (case '
    set @sql = @sql + '                                 when Y.name = ''tinyint'' then 255 '
    set @sql = @sql + '                                 when Y.name = ''smallint'' then 32767 '
    set @sql = @sql + '                                 when Y.name = ''int'' then 2147483647 '
    set @sql = @sql + '                                 when Y.name = ''bigint'' then 9223372036854775807 '
    set @sql = @sql + '                                 when Y.name = ''decimal'' then (POWER(CAST(10 AS float), C.precision - 1)) '
    set @sql = @sql + '                                 when Y.name = ''numeric'' then (POWER(CAST(10 AS float), C.precision - 1)) '
    set @sql = @sql + '                                 else 1 '
    set @sql = @sql + '                             end) > @alert_rate '
    set @sql = @sql + ' ORDER BY '
    set @sql = @sql + '     T.name, C.column_id '

    -- useと実行したい処理を同一コンテキスト内で実行
    execute sp_executesql @sql

FETCH NEXT FROM CMyCursor INTO
    @Database
END

CLOSE CMyCursor
DEALLOCATE CMyCursor

こちらをSQLServerのジョブで定期実行してメールを受け取る等の運用にしておくと良いかと思います。

ID値を再設定したい場合は、DBCC CHECKDBを使えばOKです。

--例
DBCC CHECKIDENT(TableName, RESEED, 0)