Table Partition View partition data DMVs part 2

Code to view the partition table data.

use Test_Tbl_Partition
go

/*

-- find no.of partition table
select object_schema_name(i.object_id) as [schema],
    object_name(i.object_id) as [object],
    i.name as [index],
    s.name as [partition_scheme]
    from sys.indexes i
    join sys.partition_schemes s on i.data_space_id = s.data_space_id


*/


go
SELECT 
ISNULL(quotename(ix.name),'Heap') as IndexName 
,ix.type_desc as type
,prt.partition_number
,case when ix.index_id < 2 then prt.rows else 0 END as Rows
,cast (rv.value as datetime2(7)) as [Upper_Boundary]

,prt.data_compression_desc
,ps.name as PartitionScheme
,pf.name as PartitionFunction
,fg.name as FilegroupName
,au.TotalMB
,au.UsedMB
,case when pf.boundary_value_on_right = 1 then 'less than value column' when pf.boundary_value_on_right is null then '' else 'less than or equal to value column' End as Comparison
,fg.name as FileGroup

FROM sys.partitions prt
inner join sys.indexes ix
on ix.object_id = prt.object_id and
ix.index_id = prt.index_id
inner join sys.data_spaces ds
on ds.data_space_id = ix.data_space_id
left join sys.partition_schemes ps
on ps.data_space_id = ix.data_space_id
left join sys.partition_functions pf
on pf.function_id = ps.function_id
left join sys.partition_range_values rv
on rv.function_id = pf.function_id AND
rv.boundary_id = prt.partition_number
left join sys.destination_data_spaces dds
on dds.partition_scheme_id = ps.data_space_id AND
dds.destination_id = prt.partition_number
left join sys.filegroups fg
on fg.data_space_id = ISNULL(dds.data_space_id,ix.data_space_id)
inner join (select str(sum(total_pages)*8./1024,10,2) as [TotalMB]
,str(sum(used_pages)*8./1024,10,2) as [UsedMB]
,container_id
from sys.allocation_units
group by container_id) au
on au.container_id = prt.partition_id
WHERE prt.OBJECT_ID like object_id(N'[dbo].[tbl_partition_test]')
--where prt.OBJECT_ID = object_id('C1810429.STYTOTAL_RAW_Intermediate_Staging')
and ix.type_desc in ('heap','CLUSTERED')
order by prt.partition_number,ISNULL(quotename(ix.name),'Heap')


/*

-- min nd max
select min([Date_Col]) as [Min_Date_Stored_PS_per_Year],max([Date_Col]) as [Max_Date_Stored_PS_per_Year] , count([Date_Col])as [No_of_Rows],
$partition.PF_myDateRange([Date_Col]) as [partition]
from [dbo].[tbl_partition_test]
group by  $partition.PF_myDateRange([Date_Col]) 


select [Date_Col] , $partition.PF_myDateRange([Date_Col]) as [partition]
from [tbl_partition_test] 
where $partition.PF_myDateRange([Date_Col]) in (6)
order by 1

*/