Stockage des tables et index

Cette requête renvoie les informations détaillées sur le stockage des tables et index de la base courante.

/*-------------------------------------------------------------------
[SCRIPT] tables and index storage   
[DATABASE] current
[DESCRIPTION] detailed informations about tables and index storage
[MAJ PAR] DATAFLY - Arian Papillon
-------------------------------------------------------------------*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT   OBJECT_NAME(i.object_id) AS TableName
       , i.index_id
       , i.name AS IndexName
       , i.type_desc AS IndexType
       , p.partition_number AS pnum
       , a.type_desc AS page_type_desc
       , s.name AS FileGroupName
       , fg.is_read_only
       , df.physical_name -- lignes dupliquées si plusieurs fichiers
       , p.rows
       , a.total_pages AS pages
       , a.total_pages * 8 / 1024 AS Size_Mo
       , p.data_compression_desc
       , sch.name AS partition_scheme
       , fn.name AS function_name
       , sprv.value AS limite
       , CASE WHEN fn.boundary_value_on_right = 1 THEN 'RIGHT'
              ELSE 'LEFT'
         END AS Side
       , sa.first_page
       , sa.root_page
       , sa.first_iam_page
FROM     sys.tables t
         JOIN sys.indexes i ON t.object_id = i.object_id
         JOIN sys.partitions p ON i.object_id = p.object_id
                                  AND i.index_id = p.index_id
         JOIN sys.allocation_units a ON p.partition_id = a.container_id
         JOIN sys.system_internals_allocation_units sa ON a.allocation_unit_id = sa.allocation_unit_id
         JOIN sys.data_spaces s ON a.data_space_id = s.data_space_id
         JOIN sys.filegroups fg ON s.data_space_id = fg.data_space_id
         JOIN sys.database_files df ON s.data_space_id = df.data_space_id ---- lignes dupliquées si plusieurs fichiers
         LEFT JOIN sys.partition_schemes sch ON i.data_space_id = sch.data_space_id
         LEFT JOIN sys.partition_functions fn ON sch.function_id = fn.function_id
         LEFT JOIN sys.partition_range_values sprv ON fn.function_id = sprv.function_id
                                                      AND sprv.boundary_id = p.partition_number
WHERE    t.type = 'U'
--    AND OBJECT_NAME(i.object_id) = 'xyz'  -- sélection de la table
--    AND A.type_desc = 'IN_ROW_DATA'
--    AND i.type_desc IN ('HEAP', 'CLUSTERED')
ORDER BY TableName
       , i.index_id
       , pnum;

/*-------------------------------------------------------------------

[SCRIPT] tables and index storage

[DATABASE] current

[DESCRIPTION] detailed informations about tables and index storage

[MAJ PAR] DATAFLY - Arian Papillon

-------------------------------------------------------------------*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT OBJECT_NAME(i.object_id) AS TableName

, i.index_id

, i.name AS IndexName

, i.type_desc AS IndexType

, p.partition_number AS pnum

, a.type_desc AS page_type_desc

, s.name AS FileGroupName

, fg.is_read_only

, df.physical_name -- lignes dupliquées si plusieurs fichiers

, p.rows

, a.total_pages AS pages

, a.total_pages * 8 / 1024 AS Size_Mo

, p.data_compression_desc

, sch.name AS partition_scheme

, fn.name AS function_name

, sprv.value AS limite

, CASE WHEN fn.boundary_value_on_right = 1 THEN 'RIGHT'

ELSE 'LEFT'

END AS Side

, sa.first_page

, sa.root_page

, sa.first_iam_page

FROM sys.tables t

JOIN sys.indexes i ON t.object_id = i.object_id

JOIN sys.partitions p ON i.object_id = p.object_id

AND i.index_id = p.index_id

JOIN sys.allocation_units a ON p.partition_id = a.container_id

JOIN sys.system_internals_allocation_units sa ON a.allocation_unit_id = sa.allocation_unit_id

JOIN sys.data_spaces s ON a.data_space_id = s.data_space_id

JOIN sys.filegroups fg ON s.data_space_id = fg.data_space_id

JOIN sys.database_files df ON s.data_space_id = df.data_space_id ---- lignes dupliquées si plusieurs fichiers

LEFT JOIN sys.partition_schemes sch ON i.data_space_id = sch.data_space_id

LEFT JOIN sys.partition_functions fn ON sch.function_id = fn.function_id

LEFT JOIN sys.partition_range_values sprv ON fn.function_id = sprv.function_id

AND sprv.boundary_id = p.partition_number

WHERE t.type = 'U'

-- AND OBJECT_NAME(i.object_id) = 'xyz' -- sélection de la table

-- AND A.type_desc = 'IN_ROW_DATA'

-- AND i.type_desc IN ('HEAP', 'CLUSTERED')

ORDER BY TableName

, i.index_id

, pnum;