by Amol
22. February 2011 19:16
SELECT
i.object_id,
i.name AS IndexName,
p.partition_number,
fg.name AS FileGroupName,
value,
ps.name AS SchemaName,
f.name FunctionName
FROM sys.partitions p
JOIN sys.indexes i
ON (p.object_id = i.object_id
AND p.index_id = i.index_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)
LEFT JOIN sys.partition_range_values rv
ON (f.function_id = rv.function_id
AND p.partition_number = rv.boundary_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)
WHERE i.index_id < 2
AND i.object_id = Object_Id('<ObjectName>')