;with cte1 as (
select
quotename(object_schema_name(ic.object_id)) + N'.' + quotename(object_name(ic.object_id)) as table_name,
ic.name as identity_column_name,
t.name as type_name,
cast(ic.seed_value as decimal(38,0)) as seed_value,
cast(ic.increment_value as decimal(38,0)) as increment_value,
-- ic.last_value is null if no rows have ever been added
-- ident_current is the seed if no rows have ever been added
cast(ic.last_value as decimal(38,0)) as last_value,
isnull(cast(ic.last_value as decimal(38,0)) + cast(ic.increment_value as decimal(38,0)), cast(ic.seed_value as decimal(38,0))) as next_value,
cast (case
when t.name = 'tinyint' and ic.increment_value > 0 then 255.0
when t.name = 'tinyint' and ic.increment_value < 0 then 0.0
when t.name = 'smallint' and ic.increment_value > 0 then 32767.0
when t.name = 'smallint' and ic.increment_value < 0 then -32768.0
when t.name = 'int' and ic.increment_value > 0 then 2147483647.0
when t.name = 'int' and ic.increment_value < 0 then -2147483648.0
when t.name = 'bigint' and ic.increment_value > 0 then 9223372036854775807.0
when t.name = 'bigint' and ic.increment_value < 0 then -9223372036854775808.0
-- Note: Because of the way SQL Server does decimal maths this script can't handle decimal/numeric identity columns with precision above 31.
when t.name in ('decimal', 'numeric') and ic.precision <=31 and ic.increment_value > 0 then cast(replicate('9', ic.precision) as decimal(38,0))
when t.name in ('decimal', 'numeric') and ic.precision <=31 and ic.increment_value < 0 then cast('-' + replicate('9', ic.precision) as decimal(38,0))
end as decimal(38,0)) as range_end
from
sys.identity_columns ic
inner join sys.types t on t.user_type_id = ic.user_type_id
inner join sys.tables tbl on tbl.object_id = ic.object_id -- just user tables
), cte2 as (
select
c.*,
floor((abs(c.range_end - c.seed_value + c.increment_value)) / abs(c.increment_value)) as total_capacity_rows,
floor((abs(c.range_end - c.next_value + c.increment_value)) / abs(c.increment_value)) as remaining_capacity_rows
from
cte1 c
)
select
c.*,
c.remaining_capacity_rows / c.total_capacity_rows * 100.0 as remaining_capacity_percent
from
cte2 c
order by
remaining_capacity_percent desc,
c.table_name