Rows in a partitioned table

I'm punting this query out here so I can reference it from work. For the current project, we are evaluating whether partitioning tables will help us (as well as using multiple data files). One of the warnings I've come across for partitioning is to make sure your partition function is doing what you think it should for a given value. Sounds bloody stupid, I mean how can you bone that? Except of course, I did first time out the gate.

This query ties together a variety of system tables to help me determine if my partition function is working as expected and assigning data into the correct file groups. If there are no partitions, this query won't return anything.

-- This ugly as sin query helps determine how
-- many rows are in tables that use 
-- partitioning
SELECT
    SCHEMA_NAME(T.schema_id) AS [schema_name]
,   T.name AS table_name
,   FDS.name AS file_group_name
,   SP.partition_number
,   PS.name AS partition_scheme
,   PF.name AS partition_function
,   SP.rows AS row_in_partition

FROM
    -- identify all of the tables
    sys.tables T 
    -- Indexes provide data_space_id which is
    -- critial to navigating everything else    
    INNER JOIN 
        sys.indexes SI 
        ON SI.object_id = T.object_id
    INNER JOIN
        sys.partitions SP
        ON SP.index_id = SI.index_id
        AND SP.object_id = T.object_id

    -- Data spaces provides the partition scheme
    INNER JOIN 
        sys.data_spaces DS 
        ON DS.data_space_id = SI.data_space_id
    -- Partition Scheme allows for linking to Partition function
    INNER JOIN
        sys.partition_schemes PS
        ON PS.data_space_id = DS.data_space_id
    INNER JOIN
        sys.partition_functions PF
        ON PF.function_id = PS.function_id
    INNER JOIN
        sys.partition_parameters PP
        ON PP.function_id = PF.function_id

    -- destination data spaces provides the translation between
    -- the partition scheme and the associated file groups
    -- also shows where the those file groups fall w.r.t. partition
    INNER JOIN
        sys.destination_data_spaces DDS
        ON DDS.partition_scheme_id = DS.data_space_id
        AND DDS.destination_id = SP.partition_number
    -- Back to data spaces to identify the names of the
    -- file group name
    INNER JOIN
        sys.data_spaces FDS
        ON FDS.data_space_id = DDS.data_space_id
    -- file groups to retrieve the name of the groups
    -- in case data files were not in line with fg
    INNER JOIN
        sys.filegroups FG
        ON FG.data_space_id = DDS.data_space_id
--inner join
--    sys.partition_functions PF
--    ON PF.function_id = DS.data_space_id
--inner join
--    sys.database_files DF
--    ON DF.file_guid = FG.filegroup_guid
ORDER BY
    SCHEMA_NAME(T.schema_id) 
,   T.name 
,   SP.partition_number

For my example, I am splitting data from a table based on fiscal year and for the demo set, I have an equal distribution of year values from 2008 to 2011. Yes, the names indicate the 2008 data is stored in the 2009 file groups and so on. See note above about verifying you are doing it correctly ;)

schema_name	table_name	file_group_name	partition_number	partition_scheme	partition_function	row_in_partition
dbo	PARTITIONED_DATA	FY_2008	1	FiscalYearPS	FiscalYearPF	0
dbo	PARTITIONED_DATA	FY_2009	2	FiscalYearPS	FiscalYearPF	53
dbo	PARTITIONED_DATA	FY_2010	3	FiscalYearPS	FiscalYearPF	54
dbo	PARTITIONED_DATA	FY_2011	4	FiscalYearPS	FiscalYearPF	54
dbo	PARTITIONED_DATA	FY_2012	5	FiscalYearPS	FiscalYearPF	54
dbo	PARTITIONED_DATA	FY_2013	6	FiscalYearPS	FiscalYearPF	0