# 1. Table and index partition metadata can be retrieved using SQL below use AdventureWorks go SELECT OBJECT_NAME([object_id]) AS table_name, * FROM sys.partitions WHERE [object_id] = OBJECT_ID('dbo.ErrorLog') ORDER BY index_id, partition_number # Results # table_name partition_id object_id index_id partition_number hobt_id rowsfilestream_filegroup_id data_compression # errorLog 72057594112245760 1707153127 0 1 7205759411224576 0 0 0 # errorLog 72057594112311296 1707153127 0 2 72057594112311296 0 0 0 # errorLog 72057594112376832 1707153127 0 3 72057594112376832 0 0 0 # errorLog 72057594112442368 1707153127 0 4 72057594112442368 0 0 0 # 2. Create a view to return details about a partitioned table or index use AdventureWorks go CREATE VIEW Partition_Info AS SELECT OBJECT_NAME(i.object_id) as Object_Name, i.name AS Index_Name, p.partition_number, fg.name AS Filegroup_Name, rows, dps.in_row_data_page_count + dps.lob_used_page_count + dps.row_overflow_used_page_count as total_pages, CASE boundary_value_on_right WHEN 1 THEN 'less than' ELSE 'less than or equal to' END as 'comparison' , rv.value, CASE WHEN ISNULL(rv.value, rv2.value) IS NULL THEN 'N/A' ELSE CASE WHEN boundary_value_on_right = 0 AND rv2.value IS NULL THEN 'Greater than or equal to' WHEN boundary_value_on_right = 0 THEN 'Greater than' ELSE 'Greater than or equal to' END + ' ' + ISNULL(CONVERT(varchar(15), rv2.value), 'Min Value') + ' ' + + CASE boundary_value_on_right WHEN 1 THEN 'and less than' ELSE 'and less than or equal to' END + ' ' + + ISNULL(CONVERT(varchar(15), rv.value), 'Max Value') END as 'TextComparison' FROM sys.partitions p JOIN sys.indexes i ON p.object_id = i.object_id and p.index_id = i.index_id LEFT JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id LEFT JOIN sys.partition_functions f ON f.function_id = ps.function_id LEFT JOIN sys.partition_range_values rv ON f.function_id = rv.function_id AND p.partition_number = rv.boundary_id LEFT JOIN sys.partition_range_values rv2 ON f.function_id = rv2.function_id AND p.partition_number - 1= rv2.boundary_id LEFT JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number LEFT JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id JOIN sys.dm_db_partition_stats dps ON dps.partition_id = p.partition_id WHERE i.index_id <2 -- Example of use: SELECT * FROM Partition_Info WHERE Object_Name = 'dbo.ErrorLog' ORDER BY Object_Name, partition_number # Results # Object_Name Index_Name partition Filegroup_Name rows total_pages comparison value TextComparison # errorLog NULL 1 Filegroup_201301 0 0 less than or equal to 2013-01-31 23:59:59.997 Greater than or equal to Min Value and less than or equal to Jan 31 2013 11: # errorLog NULL 2 Filegroup_201302 0 0 less than or equal to 2013-02-28 23:59:59.997 Greater than Jan 31 2013 11: and less than or equal to Feb 28 2013 11: # errorLog NULL 3 Filegroup_201303 0 0 less than or equal to 2013-03-31 23:59:59.997 Greater than Feb 28 2013 11: and less than or equal to Mar 31 2013 11: # errorLog NULL 4 Filegroup_201304 0 0 less than or equal to 2013-04-30 23:59:59.997 Greater than Mar 31 2013 11: and less than or equal to Apr 30 2013 11: