How to check list views that has more than 12 lookup columns in SharePoint

  • Article

So I was on a customer who asked me which lists, actually views, has more than 12 lookup fields, that we all know impacts directly in our SharePoint performance. Here is it, enjoy!

 
param(
    [Parameter(Mandatory = $false)]
    $fooparameter = "foovalue"
    )

$spsnapin = Get-PSSnapin | ? {$_.Name -eq "Microsoft.SharePoint.PowerShell"}
if (!$spsnapin) {Add-PSSnapin "Microsoft.SharePoint.PowerShell"}

$badviews = @()

get-spsite | get-spweb | % {
        $site = $_;

        Write-Verbose "Analysing site $($site.Title) ..."

        $lists = $site.Lists;
        $lists | % {
                $list = $_
                $list.Views | % {
                    $view = $_
                    $fields = $view.ViewFields
                    $count = 0
                    $fields | % {
                        $fieldname = $_
                        if ($list.Fields[$fieldname].Type -eq "Lookup")
                        {
                            $count++
                        }
                    }
                    
                    if ($count -gt 12){
                        $badview = New-Object PSObject
                        $badview | Add-Member -MemberType NoteProperty -Name "SiteUrl" -Value $site.Url
                        $badview | Add-Member -MemberType NoteProperty -Name "ListName" -Value $list.Title
                        $badview | Add-Member -MemberType NoteProperty -Name "ViewName" -Value $view.Title
                        $badview | Add-Member -MemberType NoteProperty -Name "LookupColumns" -Value $count
                        $badviews += $badview

                }
            }
        }
    }

$badviews