Pull dm_db_partition_stats from DB with many partitions

partition_stats_loop.sql

set nocount on;

drop table if exists #objects;
drop table if exists #ddps;

select object_id, identity(int,1,1) as id
into #objects
from sys.objects as o
where o.type = 'U' /* USER_TABLE */
and o.is_ms_shipped = 0;

select top 0 *
into #ddps
from sys.dm_db_partition_stats as ddps;

declare @oid int
		,@id int = 1
		,@max int = (select max(id) from #objects as o);

while @id <= @max
begin
	select	@oid = o.object_id
	from	#objects as o
	where	o.id = @id;

	insert into #ddps
	select *
	from sys.dm_db_partition_stats as ddps
	where ddps.object_id = @oid;

	set @id += 1;
end;

select		o.name as TableName
			,coalesce(i.name,'# HEAP #') as IndexName
			,count(*) as PartitionCount
			,sum(d.used_page_count) * 8 / 1024. / 1024. as SpaceUsedGB
			,sum(d.row_count) as Rows
from		sys.objects as o
join		#ddps as d
on			o.object_id = d.object_id
join		sys.indexes as i
on			i.index_id = d.index_id
and			i.object_id = o.object_id
group by	o.name
			,i.name
order by	4 desc;