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