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 https://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

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

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

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

    Thanks.

    Reply
  • 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
  • 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
    • 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

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