Find biggest tables in Partition Scheme and Filegroup

• Neeraj Mittal

  Mr or Mrs. 500

  Points: 536

  More actions

  July 19, 2011 at 3:35 am

  #254669

  Hi,

  I have SQL Server 2008 SP1. I am looking the easy way to find the large tables in particular Partition Scheme as well as filegroup. Any quick help is highly appreciated.

  Thanks

  Neeraj

• Jannie-186227

  SSCrazy

  Points: 2319

  More actions

  July 19, 2011 at 11:17 pm

  #1355349

  Hi

  You can look at joining sys.partitions to sys.allocation_units.

  Once you have that done you can just sum that data pages.

  that will give you the physical size.

  If you want the number of rows there is a function for partitions that will do this.

  USE AdventureWorks2008R2 ;

  GO

  SELECT $PARTITION.TransactionRangePF1(TransactionDate) AS Partition,

  COUNT(*) AS [COUNT] FROM Production.TransactionHistory

  GROUP BY $PARTITION.TransactionRangePF1(TransactionDate)

  ORDER BY Partition ;