En plus lisible (je me suis fais avoir pour mon premier poste).
WITH table_space_usage --Création d'une CTE pour reccupérer les informations voulues
( schema_name , table_name, index_name, used, reserved, ind_rows, tbl_rows)
as (
select s.name,
o.name,
coalesce (i.name,'HEAP'),-- Les tables HEAP n'ont pas d'index Cluster
p.used_page_count * 8,
p.reserved_page_count * 8,
p.row_count,
case when i.index_id in(0,1)then p.row_count else 0 end
FROM sys.dm_db_partition_stats as p
INNER JOIN sys.objects as o
on o.object_id= p.object_id
INNER JOIN sys.schemas as s
on s.schema_id= o.schema_id
LEFT OUTER JOIN sys.indexes as i
on i.object_id= p.object_idand i.index_id = p.index_id
WHERE o.type_desc ='USER_TABLE'
AND o.is_ms_shipped = 0
) -- puis intérrogation de la CTE avec mise en forme
select t.schema_name, t.table_name,
coalesce (t.index_name,'TOTAL')as index_name,
sum (t.used)as used_in_kb,
sum (t.reserved)as reserved_in_kb,
case grouping (t.index_name)when 0 thensum(t.ind_rows)elsesum(t.tbl_rows)endasrows
FROM table_space_usage as t
GROUP BY t.schema_name, t.table_name, t.index_name
WITH ROLLUP -- Attention cette option est en cours de déprécation !
ORDER BY
grouping ( t.schema_name), t.schema_name,
grouping ( t.table_name), t.table_name,
grouping ( t.index_name), t.index_name