Post
Ask Question

Script To Get Lists Exceeding The List View Threshold Of 5000 Items

Well, we all know SharePoint imposes limits on the list view threshold. If any list exceeds the limit, it can be a cause for performance degradation due to long running queries at the back-end [SQL].

Therefore, it’s fair to get the report, which gives us information about the lists, which are exceeding the limit of 5000 items.

The script given below provides the report.

  1. Clear-Host  
  2. Write-Host -ForegroundColor Cyan "============================================================="  
  3. Write-Host -ForegroundColor Magenta "              SharePoint List Threshold Script               "  
  4. Write-Host -ForegroundColor Cyan "============================================================="  
  5. Function PromptForScopeSelections  
  6. {  
  7.     $promptTitle = "Scope selection"  
  8.     $promptMessage = "Please select the option to set scope for generating threshold report"  
  9.   
  10.     $optionFarm = New-Object System.Management.Automation.Host.ChoiceDescription "&Farm", `  
  11.     "Generate list view threshold report for entire farm"  
  12.     $optionFarm.HelpMessage = "This option will generate list threshold report for entire farm"  
  13.   
  14.     $optionWebApp = New-Object System.Management.Automation.Host.ChoiceDescription "&Web Application", `  
  15.     "Generate list view threshold report for single web application"  
  16.     $optionWebApp.HelpMessage = "This option will generate list threshold report for single web application"  
  17.       
  18.     $optionSiteCollection = New-Object System.Management.Automation.Host.ChoiceDescription "&Site Collection", `  
  19.     "Generate list view threshold report for single site collection"  
  20.     $optionSiteCollection.HelpMessage = "This option will generate list threshold report for single site collection"  
  21.   
  22.     $promptOptions = [System.Management.Automation.Host.ChoiceDescription[]]($optionFarm, $optionWebApp, $optionSiteCollection)  
  23.   
  24.     $promptResult = $host.ui.PromptForChoice($promptTitle, $promptMessage, $promptOptions, 0)  
  25.     Return $promptResult  
  26. }  
  27.  
  28. #Load the SharePoint snap-in in PowerShell if it is not loaded already  
  29. Write-Host -ForegroundColor Yellow "Please wait while SharePoint snap-in is being loaded..."  
  30. Add-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue  
  31. if((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -ne $null)  
  32. {  
  33.     Write-Host -ForegroundColor Green "SharePoint snap-in has been loaded successfully..."  
  34.  
  35.     #Call the function to prompt for scope selection.  
  36.     $UserOption = 3  
  37.     $UserOptionAccepted = $false  
  38.     While($UserOptionAccepted -ne $true)  
  39.     {  
  40.         $UserOption = PromptForScopeSelections  
  41.         if($UserOption -ge 0 -and $UserOption -le 3)  
  42.         {  
  43.             Write-Host -ForegroundColor Green "Input has been accepted."  
  44.             switch ($UserOption)  
  45.             {  
  46.                 0 {Write-Host -ForegroundColor DarkCyan "You selected farm."}  
  47.                 1 {Write-Host -ForegroundColor DarkCyan "You selected web application."}  
  48.                 2 {Write-Host -ForegroundColor DarkCyan "You selected site collection."}  
  49.             }  
  50.             $UserOptionAccepted = $true  
  51.         }  
  52.         elseif($UserOption -lt 0 -or $UserOption -ge 3 -or $UserOption -eq "" -or $UserOption -eq $null)  
  53.         {  
  54.             Write-Host -ForegroundColor Red "Invalid input..."  
  55.         }  
  56.     }  
  57.     if($UserOptionAccepted -eq $true)  
  58.     {  
  59.         $ScriptPath = Split-Path $MyInvocation.MyCommand.Path  
  60.         $ScriptDate = Get-Date -Format "dd-MMM-yyyy"  
  61.         $ScriptTime = Get-Date -Format "hh-mm-ss"  
  62.         if($UserOption -eq 0)  
  63.         {  
  64.             $ThresholdReportPath = $ScriptPath + "\" + "SharePointFarm_LVT_" + $ScriptDate + "_" + $ScriptTime + ".csv"  
  65.         }  
  66.         elseif($UserOption -eq 1)  
  67.         {  
  68.             $WebAppAccepted = $false  
  69.             while($WebAppAccepted -ne $true)  
  70.             {  
  71.                 $WebAppURL = Read-Host -Prompt "Please Enter Web Application URL"  
  72.                 if(($WebApplication = Get-SPWebApplication $WebAppURL -ErrorAction SilentlyContinue) -ne $null)  
  73.                 {  
  74.                     Write-Host -ForegroundColor Green "Web Application URL has been accepted..."  
  75.                     $WebAppTitle = $WebApplication.Name.ToString().Replace(" ","")  
  76.                     $WebAppAccepted = $true  
  77.                 }  
  78.                 else  
  79.                 {  
  80.                     Write-Host -ForegroundColor Red "Invalid input, please provide valid Web Application URL"  
  81.                 }  
  82.             }  
  83.             $ThresholdReportPath = $ScriptPath + "\" + "WebApplication_" + $WebAppTitle + "_LVT_" + $ScriptDate + "_" + $ScriptTime + ".csv"  
  84.         }  
  85.         elseif($UserOption -eq 2)  
  86.         {  
  87.             $SiteAccepted = $false  
  88.             while($SiteAccepted -ne $true)  
  89.             {  
  90.                 $SiteCollURL = Read-Host -Prompt "Please Enter Site Collection URL"  
  91.                 if(($SiteCollection = Get-SPSite $SiteCollURL -ErrorAction SilentlyContinue) -ne $null)  
  92.                 {  
  93.                     Write-Host -ForegroundColor Green "Site Collection URL has been accepted..."  
  94.                     $SiteTitle = $SiteCollection.RootWeb.Name.ToString().Replace(" ","")  
  95.                     $SiteAccepted = $true  
  96.                 }  
  97.                 else  
  98.                 {  
  99.                     Write-Host -ForegroundColor Red "Invalid input, please provide valid Site Collection URL"  
  100.                 }  
  101.             }  
  102.             $ThresholdReportPath = $ScriptPath + "\" + "SiteCollection_" + $SiteTitle + "_LVT_" + $ScriptDate + "_" + $ScriptTime + ".csv"  
  103.         }  
  104.         #Create Output File Headers  
  105.         "Site URL`tList Title`tItem Count" | Out-File $ThresholdReportPath  
  106.         $ListCounter = 0  
  107.         if($UserOption -eq 0)  
  108.         {  
  109.             $WebApps = Get-SPWebApplication   
  110.             ForEach($WebApp in $WebApps)  
  111.             {  
  112.                 $Sites = Get-SPSite -Limit All -WebApplication $WebApp.URL  
  113.                 ForEach($Site in $Sites)  
  114.                 {  
  115.                     ForEach($Web in $Site.AllWebs)  
  116.                     {  
  117.                         ForEach($List in $Web.Lists)  
  118.                         {  
  119.                             $Items = 0  
  120.                             $Items = $List.ItemCount  
  121.                             if($Items -ge 5000)  
  122.                             {  
  123.                                 $ListCounter++  
  124.                                 Write-Host -BackgroundColor DarkCyan "No. of lists having more than 5K items: $ListCounter `r" -NoNewline   
  125.                                 $Web.URL + "`t" + $List.Title + "`t" + $Items | Out-File $ThresholdReportPath -Append  
  126.                             }  
  127.                         }  
  128.                     }  
  129.                 }  
  130.             }     
  131.         }  
  132.         elseif($UserOption -eq 1)  
  133.         {  
  134.             $Sites = Get-SPSite -Limit All -WebApplication $WebApplication.URL  
  135.             ForEach($Site in $Sites)  
  136.             {  
  137.                 ForEach($Web in $Site.AllWebs)  
  138.                 {  
  139.                     ForEach($List in $Web.Lists)  
  140.                     {  
  141.                         $Items = $List.ItemCount  
  142.                         if($Items -ge 5000)  
  143.                         {  
  144.                             $ListCounter++  
  145.                             Write-Host -BackgroundColor DarkCyan "No. of lists having more than 5K items: $ListCounter `r" -NoNewline   
  146.                             $Web.URL + "`t" + $List.Title + "`t" + $Items.TOString() | Out-File $ThresholdReportPath -Append  
  147.                         }  
  148.                     }  
  149.                 }  
  150.             }  
  151.         }     
  152.         elseif($UserOption -eq 2)  
  153.         {  
  154.             ForEach($Web in $SiteCollection.AllWebs)  
  155.             {  
  156.                 ForEach($List in $Web.Lists)  
  157.                 {  
  158.                     $Items = $List.ItemCount  
  159.                     if($Items -ge 5000)  
  160.                     {  
  161.                         $ListCounter++  
  162.                         Write-Host -BackgroundColor DarkCyan "No. of lists having more than 5K items: $ListCounter `r" -NoNewline   
  163.                         $Web.URL + "`t" + $List.Title + "`t" + $Items | Out-File $ThresholdReportPath -Append  
  164.                     }  
  165.                 }  
  166.             }     
  167.         }  
  168.         Write-Host "                                                              "  
  169.         Write-Host -ForegroundColor Cyan "Script Execution Completed..."  
  170.         Write-Host -ForegroundColor DarkMagenta "Output File Path is: " -NoNewline  
  171.         Write-Host -ForegroundColor Gray $ThresholdReportPath  
  172.     }  
  173.     else  
  174.     {  
  175.         Write-Host -ForegroundColor Red "Scope selection was not valid, terminating the exectuion..."  
  176.         Return  
  177.     }  
  178. }  
  179. else  
  180. {  
  181.     Write-Host -ForegroundColor Red "SharePoint snap-in could not be loaded, terminating the execution..."  
  182. }  

 

Inputs

  1. Scope Selection: It provides you three options, as mentioned below. If you want a report for the entire Farm, press “F”. Similarly “W” or “S” for Web Application and Site Collection respectively.

    • Farm
    • Web Application
    • Site Collection

  2. If you select Web Application, then the next input you should provide is Web Application URL.
  3. If you select Site Collection, then the next input you should provide is Site Collection URL.

Once the required inputs are provided, it will go through each list in the sites and check, if it exceeds the limit of 5000 items. If yes, it will add that list to the report.

After the execution is completed, it will provide you the location, where the report is generated.

You can have a look at the report and identify the large lists. Later, remediate the lists, as per Microsoft’s recommended methods to ensure the list. The views don’t retrieve more than 5000 items at a time.

Hence, you can contribute to smooth performance for SharePoint Farm.

I hope this helps. Thanks for reading and using the script.
Next Recommended Reading Getting List items In SharePoint Hosted App