Find All Lists which Exceeds List View Lookup threshold in SharePoint

Requirement:
Resource throttling feature in SharePoint controls the resource usage on SharePoint Farm for optimum usage. My requirement was to find all lists exceeding the configured list view lookup threshold limit in SharePoint.

PowerShell script to find All Lists which are Exceeding List View Lookup threshold:

This script scans all lists in the web application and produces a CSV file with all List Name, URLs, and lookup columns where the list exceeds the maximum count configured in web application throttling settings in SharePoint 2013 central administration site.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
 
#Get the Web Application
$WebApp = Get-SPWebApplication "https://portal.crescent.com"
 
#Array to Hold Result - PSObjects
$ListResults = @()

#Get the Throttling Limit of the Web App
$Threshold = $WebApp.MaxQueryLookupFields
 
    foreach($Site in $WebApp.Sites)
    {
        foreach($Web in $Site.AllWebs)
        {
            Write-host "Scanning site:"$Web.URL
             
            foreach($List in $Web.Lists)
            {
                #Get Number of Lookup Fields
                $LookupFields = $List.Fields | Where { $_.TypeDisplayName -eq "Lookup" -and $_.Hidden -eq $false}
                if($LookupFields.Count -gt $Threshold)
                {
                    $Result = New-Object PSObject
                    $Result | Add-Member NoteProperty Title($list.Title)
                    $Result | Add-Member NoteProperty URL($web.URL)
                    $Result | Add-Member NoteProperty Count($LookupFields.Count)
                     
                    #Add the object with property to an Array
                    $ListResults += $Result
                }
            }
        }
    }

Write-host "Total Number of Lists Exceeding Lookup Threshold:"$ListResults.Count -f Green
#Export the result Array to CSV file
$ListResults | Export-CSV "c:\ListData.csv" -NoTypeInformation

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

Leave a Reply

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