Script to get table and its partition details

SELECT fg.groupname FileGroupName,sf.name [FileLocation],sf.fileid,
[filename],ds.type,ds.type_desc,object_name(sp.object_id) ObjectName,sp.index_id,sp.rows,ds.data_space_id
,au.type_desc,au.total_pages,au.used_pages,au.data_pages,
sp.partition_number
 FROM sys.sysfiles sf INNER JOIN sys.sysfilegroups fg ON sf.groupid=fg.groupid
 INNER JOIN sys.data_spaces ds ON fg.groupid=ds.data_space_id
 INNER JOIN sys.allocation_units au ON ds.data_space_id=au.data_space_id
 INNER JOIN sys.partitions sp ON au.container_id=sp.hobt_id 
 INNER JOIN sys.tables st ON sp.object_id=st.object_id
 --LEFT JOIN sys.partition_schemes ps ON ps.data_space_id=ds.data_space_id
 and au.container_id in (select hobt_id from sys.partitions) --where object_id=(select object_id from sys.tables))
and au.data_pages!=0 and fg.groupname!='PRIMARY'--##Exculding LOB_DATA if there is no data pages consumed --sp.hobt_id
--and sp.object_id=object_id('CDS_SessionLog')
group by sp.object_id,fg.groupname,fg.groupid,sf.name,sf.fileid,filename,ds.type,ds.type_desc,ds.data_space_id ,au.allocation_unit_id,
au.type,au.type_desc,au.container_id,au.total_pages,au.used_pages,au.data_pages,sp.index_id,
sp.rows,sp.partition_number
order by fg.groupid asc

No of rows in each partition

SELECT $PARTITION.PartitioningByMonth(ReportDate) AS Partition, 
COUNT(*) AS [COUNT] FROM Reports
GROUP BY $PARTITION.PartitioningByMonth(ReportDate)
ORDER BY Partition ;
GO

Partition Schemes in a Database

select * from sys.partition_schemes

Partition information

select * from sys.partitions

Partition schem, function and Range

select * from sys.partition_functions
go
select * from sys.partition_schemes
go
select * from sys.partition_range_values

Database Filegroups

select * from sys.sysfilegroups

Partition Enabled Tables

DECLARE @PartitionSchemeId varchar (max)
 SELECT @PartitionSchemeId=data_space_id from sys.data_spaces where type='PS'
Print @PartitionSchemeId

SELECT name,object_id FROM sys.tables where lob_data_space_id=@PartitionSchemeId