Quelles sont mes tables ou index partitionnés ? Comment sont-ils partitionnés ? Voici une requête qui détaille les éléments du partitionnement…
Voici une requête qui vous donne toutes ces informations :
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;
Voici un exemple de résultat :
Métadonnées du partitionnement< - Le lien de téléchargement du code/a>
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *