notebook/IT/SQL/SCRIPTS SQL QSA ORSYS/Méta données du partitionnement.sql
2020-11-26 18:38:25 +01:00

48 lines
1.7 KiB
SQL

SELECT s.name AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
i.name AS INDEX_NAME,
f.name AS PARTITION_FUNCTION,
ps.name AS PARTITION_SCHEMA,
p.partition_number AS PART_NUM,
fg.name AS FILE_GROUP,
rows AS ROW_COUNT,
SUM(dbf.size) OVER(PARTITION BY fg.name) AS PAGE_COUNT,
au.total_pages AS USED_PAGES,
CASE boundary_value_on_right
WHEN 1
THEN 'RIGHT'
ELSE 'LEFT'
END AS RANGE,
rv1.value AS LOW_VALUE,
rv2.value AS HIGH_VALUE
FROM sys.partitions p
JOIN sys.indexes i
ON p.object_id = i.object_id
AND p.index_id = i.index_id
JOIN sys.objects AS o
ON i.object_id = o.object_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
JOIN sys.partition_functions f
ON f.function_id = ps.function_id
JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
JOIN sys.database_files AS dbf
ON dbf.data_space_id = fg.data_space_id
JOIN sys.allocation_units au
ON au.container_id = p.partition_id
LEFT OUTER JOIN sys.partition_range_values rv2
ON f.function_id = rv2.function_id
AND p.partition_number = rv2.boundary_id
LEFT OUTER JOIN sys.partition_range_values rv1
ON f.function_id = rv1.function_id
AND p.partition_number - 1 = rv1.boundary_id
ORDER BY TABLE_SCHEMA,
TABLE_NAME,
INDEX_NAME,
LOW_VALUE;