Get List with Lookup Column Powershell Script SharePoint 2013 or SharePoint 2010

InfoPath alternatives for form designing SharePoint

When you are working on migration project it’s very important to have list of lookup columns as sometime lookup column migration doesn’t happen through 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.

Also read: Extract termstores in SharePoint 2013 using PowerShell

[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

Hope this will be helpful.

Similar SharePoint 2013 Tutorials


About 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.

View all posts by Sambita Rath →

Leave a Reply