Find Large SharePoint Lists & Generate Report with PowerShell

Requirement: Find large lists in SharePoint using PowerShell! Large lists are potential threats to SharePoint performance. So, wanted to audit SharePoint environment to find all large lists which are having items more than 2000 and generate a report.

find large lists in sharepoint using powershell

PowerShell to Find Large Lists and Generate Report in SharePoint:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue 

#For Output file generation
$OutputFN = "LargeListsData.csv"
#delete the file, If already exist!
if (Test-Path $OutputFN)
{ 
   Remove-Item $OutputFN 
}
#Write the CSV Headers
Add-Content $OutputFN "List Name , site Collection , Site URL , Item count"

#Get the Web Application URL
$WebAppURL = Read-Host "Enter the Web Application URL:"
$SPwebApp = Get-SPWebApplication $WebAppURL

#Loop through All Site collections, Sites, Lists
foreach($SPsite in $SPwebApp.Sites) 
{
	foreach($SPweb in $SPSite.AllWebs) 
	{
		foreach($SPlist in $SPweb.Lists) 
		{
			if($splist.ItemCount -gt 2000) 
			{
				$content = $SPlist.Title + "," + $SPsite.Rootweb.Title +"," + $SPweb.URL + "," + $SPlist.ItemCount
				add-content $OutputFN $content
			}
		}
		$SPweb.Dispose()
	}
	$SPsite.Dispose()
}
write-host "Large List report generated successfully!"

PowerShell script to find large lists and export to CSV:

Let’s do it in one line! Here is the PowerShell one-liner to find Large lists (lists with more than 2000 items) and export to CSV:

Get-SPWebApplication http://intranet.crescent.com | Get-SPSite -Limit All | Get-SPWeb -Limit All `
    | Foreach-object { $_.Lists | Select @{N="Url";E={$_.ParentWeb.url+$_.RootFolder.Url} }, Title, ItemCount } `
        | Where {$_.ItemCount -gt 2000} | sort ItemCount -Descending | Export-csv -Path "c:\largeLists.csv" -NoTypeInformation

Here is my another article written in C# to find large lists in SharePoint: Find Large Lists in SharePoint

Salaudeen Rajack

Information Technology Professional with Two decades of SharePoint Experience.

5 thoughts on “Find Large SharePoint Lists & Generate Report with PowerShell

  • January 13, 2018 at 1:37 AM

    Never mind… I found you have already covered this in a later post.
    Really good stuff!

    Thanks.

    Reply
  • January 13, 2018 at 1:34 AM

    This is very useful. How can I change this to find large documents? I’d like to iterate through every list/library and find files larger than xxxMB.

    Reply
  • October 2, 2012 at 8:04 PM

    Hello,

    When I tried to use this script. It askde follwing parmeter from me :
    cmdlet Add-Content at command pipeline position 1
    Supply values for the following parameters:
    Value[0]:

    Please help me and thanks in advance.

    Yogesh

    Reply
    • October 3, 2012 at 12:31 PM

      Yogesh,
      I’ve Seen this error when we call the function without a value to the Name parameter.

      You can try the Operator “>>” to append to text file instead.
      E.g. $SPlist.Title + “,” + $SPsite.Rootweb.Title +”,” + $SPweb.URL + “,” + $SPlist.ItemCount >> LargeListsData.csv

      Reply

Leave a Reply