PowerShell script to get all list with lookup columns in SharePoint 2013/2016/2010

This PowerShell tutorial, we will discuss how to get all list with lookup columns in SharePoint 2013/2016/2010 using PowerShell.

When you are working on the migration project it’s very important to have a list of lookup columns as sometimes lookup column migration doesn’t happen through the tool. There could be many other requirements where you need to extract the lookup column of your site.

We had extract lookup column details for the entire farm. So let’s script it. Sharing the code snippet to get the lookup column, respective list name, web URL.

PowerShell script to get all list with lookup columns in SharePoint

Below is the PowerShell script to get all lists with lookup columns in SharePoint 2013/2016/2010. If you are new to PowerShell, read an article on Working with PowerShell in SharePoint Online/2016/2013.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
try{
#Create a variable based on the current date and time
$StartTime = (Get-Date -UFormat "%Y-%m-%d_%I-%M-%S %p").tostring()

$0 = $MyInvocation.MyCommand.Definition
$dp0 = [System.IO.Path]::GetDirectoryName($0)
$output = $("$dp0\Result\ListsWithLookupColumns_" +$StartTime +".csv")

$logFile=$("$dp0\Logs\ListsWithLookupColumnsLog.txt")
Write "Script started running at " $StartTime >> $logFile

##Creating and Returning a DataTable##
function createDT()
{
###Creating a new DataTable###
$tempTable = New-Object System.Data.DataTable

##Creating Columns for DataTable##
$col1 = New-Object System.Data.DataColumn("URL")
$col2 = New-Object System.Data.DataColumn("List Name")
$col3 = New-Object System.Data.DataColumn("Field Name")

###Adding Columns for DataTable###
$tempTable.columns.Add($col1)
$tempTable.columns.Add($col2)
$tempTable.columns.Add($col3)

return ,$tempTable
}
[System.Data.DataTable]$dTable = createDT

#Initialize Workflow Count variable
$TTNExcludeLists = "Solution Gallery",
"Master Page Gallery"

$farm = [Microsoft.SharePoint.Administration.SPFarm]::Local
$websvcs = $farm.Services | where -FilterScript {$_.GetType() -eq [Microsoft.SharePoint.Administration.SPWebService]}
foreach ($websvc in $websvcs)
{
try
{
foreach ($webApplication in $websvc.WebApplications)
{
try
{
Write "`r`n Inside the loop for web application" $webApplication.Url >> $logFile
foreach($site in $webApplication.Sites)
{
try
{
Write "`r`n Inside the loop for site" $site.Url >> $logFile
foreach($web in $site.AllWebs)
{

Write "`r`n Inside the loop for web" $web.Url >> $logFile
foreach($list in $web.Lists)
{
Write "`r`n Inside the loop for the list" $list.Title >> $logFile
if(-Not ($TTNExcludeLists -Contains $list.Title))
{
foreach($fld in $list.Fields)
{
if($fld.TypeDisplayName -eq "Lookup" -and $fld.Hidden -eq $false -and $fld.FromBaseType -eq $false)
{
Write-Host "web URL: " $web.Url "ListName: " $list.Title "FeildName: " $fld.Title
$row = $dTable.NewRow()
$row["URL"] = $web.Url
$row["List Name"] = $list.Title
$row["Field Name"] = $fld.Title

$dTable.rows.Add($row)
}

}
}
}
$web.Dispose()
}
}
catch [Exception]{
Write $_.Exception|format-list -force >>$logFile
Write-Host -f red $_.Exception|format-list -force
}
finally{
if($web){
$web.Dispose()
}
}

$site.Dispose()

}
}
catch [Exception]{
Write $_.Exception|format-list -force >>$logFile
Write-Host -f red $_.Exception|format-list -force
}
finally{
if($site){
$site.Dispose();
}
}

}
}
catch [Exception]{
Write $_.Exception|format-list -force >>$logFile
Write-Host -f red $_.Exception|format-list -force
}
}

if($dTable -ne $null)
{
$dTable | Export-CSV -path $output -notype
#Write-Host "Done" -ForegroundColor Green
}
else
{
Write-Host "Could not write anything to table" -ForegroundColor red
}
}
catch [Exception]{
Write $_.Exception|format-list -force >>$logFile
Write-Host -f red $_.Exception|format-list -force
}
$EndTime = (Get-Date -UFormat "%Y-%m-%d_%I-%M-%S %p").tostring()
Write "Script stopped at" $EndTime >> $logFile

You may like following PowerShell tutorials:

Hope this will be helpful to get all list with lookup columns in SharePoint 2013/2016/2010 using PowerShell.

Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.

Sambita Rath

I have 9 years of exprience into SharePoint implementation, architecture, Administrator, development and Training.Designing Information Architecture in SharePoint 2007, 2010,2013 and Office 365.

>