RMJCS Logo
 

Check Identity Capacity

 
 
    select 
        quotename(object_schema_name(sc.object_id)) + N'.' + quotename(object_name(sc.object_id)) as table_name,
        sc.name as identity_column_name,
        t.name as identity_column_type_name,
        ident_current(quotename(object_schema_name(sc.object_id)) + N'.' + quotename(object_name(sc.object_id))) as ident_current,
        (1 - ident_current(quotename(object_schema_name(sc.object_id)) + N'.' + quotename(object_name(sc.object_id))) / 
            case t.name 
                when 'tinyint' then 255.0 
                when 'smallint' then 32767.0 
                when 'int' then 2147483647.0 
                when 'bigint' then 9223372036854775807.0
            end
        ) * 100 AS remaining_capacity_percent
    from 
        sys.columns sc 
            inner join sys.objects so on so.object_id = sc.object_id
            inner join sys.types t on t.user_type_id = sc.user_type_id
    where 
        sc.is_identity = 1 and
        so.type = 'U' 
    order by 
        remaining_capacity_percent asc
 
     
Copyright 2007-2018 RMJCS