Credits:
Author: Unknown
Date: 31 Jul 2009
Description
This code retrieves a list of key data partition metadata for all tables in a database.
** NOTE: The only data partitioning that was supported in SQL 2000 was via distributed partitioned views. They do not have the same level of functionality so there is no equivalent code.
Code
SELECT tbl.name AS TableName, p.partition_number AS PartitionNumber, prv.value AS RightBoundaryValue, CAST(p.rows AS FLOAT) AS [RowCount], fg.name AS FileGroupName, CAST(pf.boundary_value_on_right AS INT) AS RangeType, p.data_compression AS DataCompression FROM sys.tables AS tbl INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS INT) AND p.index_id=idx.index_id INNER JOIN sys.indexes AS indx ON p.object_id = indx.object_id and p.index_id = indx.index_id LEFT OUTER JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = indx.data_space_id and dds.destination_id = p.partition_number LEFT OUTER JOIN sys.partition_schemes AS ps ON ps.data_space_id = indx.data_space_id LEFT OUTER JOIN sys.partition_range_values AS prv ON prv.boundary_id = p.partition_number and prv.function_id = ps.function_id LEFT OUTER JOIN sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id or fg.data_space_id = indx.data_space_id LEFT OUTER JOIN sys.partition_functions AS pf ON pf.function_id = prv.function_id
|