Partitions / Filegroups

Hi All,
Has anyone managed to link the system views (or any other mechanisms) to
display which partition number is in which filegroup?
e.g. As follows:
Table Name Partition_Number Filegroup_Name No_Rows
Demo_Table 1 OLD_DATA 4,000,000
Demo_Table 2 MED_DATA 3,000,000
Demo_Table 3 CURRENT_DATA 50,000
Thanks in advance,
DBX1
DBX1
Have you tried
SELECT * FROM sys.partitions ?
"DBX1" <DBX1@.discussions.microsoft.com> wrote in message
news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
> Hi All,
> Has anyone managed to link the system views (or any other mechanisms) to
> display which partition number is in which filegroup?
> e.g. As follows:
> Table Name Partition_Number Filegroup_Name No_Rows
> Demo_Table 1 OLD_DATA 4,000,000
> Demo_Table 2 MED_DATA 3,000,000
> Demo_Table 3 CURRENT_DATA 50,000
> Thanks in advance,
> DBX1
|||Yup, but this lists the partition something is in, and not the filegroup a
partition resides in, the closest I have come to making this work is the
following:
-- This is not very tidy code currently, and needs to be refactored
-- Partition_Table_Example : This is the table which we are interested in,
for filtering purposes in this example
select DS.destination_id , F.name,
PIX.obj_name, PIX.partition_number, PIX.[rows], PIX.[index_id]
from sys.destination_data_spaces DS
join sys.filegroups F
on DS.data_space_id = F.data_space_id
join (
SELECT OBJECT_NAME(P.[object_id]) as obj_name,
P.partition_number, P.[rows], P.[index_id]
FROM sys.partitions P
WHERE P.[object_id] = OBJECT_ID('[dbo].[Partition_Table_Example]')
) PIX
on PIX.Partition_number = DS.Partition_ID
-- This works, the interesting thing to note is that Partition_number links to
-- Destination_ID on the Partition_ID sys.partitions view
--There may be another link required between sys.partitions and
sys.destination_data_spaces if there are multiple partition schemes/functions
-- This has only been validated where there is a single partition_scheme
"Uri Dimant" wrote:

> DBX1
> Have you tried
> SELECT * FROM sys.partitions ?
>
>
>
> "DBX1" <DBX1@.discussions.microsoft.com> wrote in message
> news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
>
>
|||sys.partitions - will give the row-count for the table/individual parititons,
so that is not an issue, Im more interested in a join based query for all the
partitioned objects, I have manged to get something close, with the prior
querys for all objects.
It is missing something currently when there are multi-partition
defined/multi-partitioned objects as it returns some incorrect data.
The problem with the enclosed query is it is limited to a single object or
requires adding for obj_ids. and does not return the filegroup a particular
item or partition resides in.
What I am particulary interested in knowing is what sliced partition resides
in which filegroup.
I am aware that there can be multiple partitions in a single filegroup.
this does not list the filegroup names.
"Dejan Sarka" wrote:

> Hi!
>
> Partitions do not map one-to-one to filegroups. You can have multiple
> partitions on a single filegroup. You can find basic info about partitions
> in sys.partitions view or with $PARTITION function, like
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> To find number of rows in different filegroups, the query gets complicated:
> WITH
> PartitionRowCount(Partition, NumberOfRowsInPartition) AS
> (
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> ),
> PartitionsDataSpaces (partition_scheme_id, Partition, data_space_id,
> DataSpaceName) AS
> (
> SELECT dds.partition_scheme_id,
> dds.destination_id,
> dds.data_space_id,
> ds.name
> FROM sys.destination_data_spaces dds
> INNER JOIN sys.indexes i
> ON dds.partition_scheme_id = i.data_space_id
> INNER JOIN sys.data_spaces ds
> ON dds.data_space_id = ds.data_space_id
> WHERE i.object_id = OBJECT_ID('PartitionTable')
> AND i.index_id < 2
> )
> SELECT pds.DataSpaceName,
> SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace
> FROM PartitionRowCount prc
> INNER JOIN PartitionsDataSpaces pds
> ON prc.Partition = pds.Partition
> GROUP BY pds.DataSpaceName;
> I hope I did not miss something.
> --
> Dejan Sarka
> http://www.solidqualitylearning.com/blogs/
>
>
|||Anyone able to validate the code below (I think this is producing the data
that I require):
select distinct object_name(SI.object_id),*
--F.name,SP.rows
from sys.destination_data_spaces DS
join sys.filegroups F on
F.data_space_id = DS.data_space_id
join sys.partition_schemes PS
on PS.data_space_id = DS.partition_scheme_id
join sys.indexes SI on
SI.data_space_id = DS.partition_scheme_id
join sys.partitions SP on
SP.object_id = SI.object_id and
SI.index_id = SP.index_id and
SP.partition_number = DS.destination_id
"DBX1" wrote:
[vbcol=seagreen]
> sys.partitions - will give the row-count for the table/individual parititons,
> so that is not an issue, Im more interested in a join based query for all the
> partitioned objects, I have manged to get something close, with the prior
> querys for all objects.
> It is missing something currently when there are multi-partition
> defined/multi-partitioned objects as it returns some incorrect data.
> The problem with the enclosed query is it is limited to a single object or
> requires adding for obj_ids. and does not return the filegroup a particular
> item or partition resides in.
> What I am particulary interested in knowing is what sliced partition resides
> in which filegroup.
> I am aware that there can be multiple partitions in a single filegroup.
> this does not list the filegroup names.
>
> "Dejan Sarka" wrote: