PowerShell: Finding all SharePoint Lists with Lookup Columns

 

Did you ever want to find all of the columns of a certain type? All Lookups, all Calculated or all Managed Metadata columns? All you have to do is look at the Fields object "TypeDisplayName" property. While the example script below is looking for Lookup columns, you could modify it to look for any kind of column.

 

Find all Lookup Columns

This will find all Lookup columns in the entire farm!

Notes:

• Many of the out of the box lists are "custom" lists and will have fields that look like user added columns. Exclude those with the $TTNExcludeLists variable.
• Field that are Hidden or FromBaseType are not typically user created and are excluded in this example.

$TTNExcludeLists = "Solution Gallery", 
                "Workflow Tasks", 
                "Master Page Gallery"

Get-SPSite -Limit All | Get-SPWeb -Limit All | 
  Select -ExpandProperty Lists | 
  Where { -Not ($TTNExcludeLists -Contains $_.Title) } | 
  Select -ExpandProperty Fields | 
  Where { $_.TypeDisplayName -eq "Lookup" -and 
          $_.Hidden -eq $false -and 
          $_.FromBaseType -eq $false } | 
  Select {$_.ParentList.ParentWebUrl}, 
         {$_.ParentList}, 
         Title

 

TypeDisplayName

If you would like to see the list of all of the column types used in your site or farm you can run a script like this:

Get-SPWeb "http://yourServer/sites/YourSite" |
  Select -ExpandProperty Lists | 
  Select -ExpandProperty Fields |
  Select TypeDisplayName –Unique |

  Sort

It may take a long time to run (WARNING!) but this will list all of the columns in the farm:

Get-SPSite -Limit All | Get-SPWeb -Limit All | 
  Select -ExpandProperty Lists | 
  Select -ExpandProperty Fields |
  Select TypeDisplayName -Unique|
  Sort TypeDisplayName

 

Here's the list I got from my test farm:

TypeDisplayName
---------------
All Day Event
Attachments
Audience Targeting
Calculated
Channel Alias
Check Double Booking
Choice
Computed
Content Type Id
Content Type ID
Counter
Cross Project Link
Date and Time
Event Type
File
Free/Busy
Guid
Hold Status
Hyperlink or Picture
Integer
Lookup
Managed Metadata
Moderation Status
Multiple lines of text
Number
Number of Likes
Number of Ratings
Out of Policy
Outcome choice
Page Separator
Permission Level
Person or Group
Publishing HTML
Publishing Image
Publishing Schedule End Date
Publishing Schedule Start Date
Rating (0-5)
Recurrence
Related Items
Resources
Single line of text
Summary Links
ThreadIndex
User Agent Substrings
Variations
Yes/No

 

.