How to quickly identify large lists with PowerShell

It’s easy, and it’s just one line (without word wrapping J).

Get-SPWebApplication http://webappurl | Get-SPSite | Get-SPWeb |% { $_.Lists | select @{n=“Url”;e={$_.RootFolder.ServerRelativeUrl} }, Title, ItemCount } | sort ItemCount -Descending

Here I’m traversing the contents of a Web Application in order to iterate over each List (in each Web, in each Site of the Web Application):

Get-SPWebApplication http://webappurl | Get-SPSite | Get-SPWeb |% { $_.Lists | … }

Then, I’m using the Select-Object command-let to add a custom object to the pipeline, built from the list data using both standard properties (Title, ItemCount) and a calculated property for the list url:

{ $_.Lists | select @{n=“Url”;e={$_.RootFolder.ServerRelativeUrl} }, Title, ItemCount }

Finally, it’s just a matter of manipulating the custom objects collections, applying sorting, filtering, grouping or any other set operations according to your specific needs.

Here’s the output you may receive:

In addition, you may choose to bring these data into Excel for further analysis, which is extremely easy to achieve adding the Export-CSV command-let at the end of the pipeline.

Here’s a sample Excel spreadsheet generated from the data above, where I’ve applied a custom number filter to the data set and I have created a line chart on the data series:

Leave a Reply

Your email address will not be published. Required fields are marked *