SQL Server Finding Row Counts in Partitions

Getting partition information out of the database is not a cut and dry operation, it requires digging into the dynamic views. Kolby Summers our SR DBA here at Zillow has done some extensive work here, and maybe he can share some of his scripts on his blog http://www.mydbalife.com The script I have here is quick and dirty for retrieving the amount of rows, and datapages used in a partition for a particular table. In the example below its for t1.



Declare @TableName varchar(100)
Set @TableName = 't1'


select @TableName, i.index_id, name, Page_count, Record_Count,
Partition_number, Index_level
from sys.dm_db_index_physical_stats(db_id(), object_id(@TableName), null, null, 'Detailed') s
inner join sys.indexes i
on i.object_id = s.object_id
group by i.index_id, name, Record_Count, Page_Count, Record_Count, Partition_number, Index_level
Having index_level = 0