script to pull cascading dropdown list from webapplication

Question

Please share me the script for pulling of lists of site collection from WA , which should contain list with cascading drop down choice.

it can be from WA or particular group of site collection.
I would like to have in excel with list name and site collection name. Please help

Answer 1

Hi @sns ,

Please run the following script as an admin :

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue  
  
#Bring your Server Name  
$ServerName = "SP"     
$WebApps = Get-SPWebApplication    
  
#Output file    
$Output = "C:\WebApplications.csv"  
  
$SiteDataCollection = @()  
  
#Array to store data   
foreach ($WebApp in $WebApps)  
{   
 $WebAppUrl = $WebApp.URL    
 $WebAppName= $WebApp.DisplayName   
 $Sites= Get-SPSite -limit all -WebApplication $WebApp.URL    
 foreach ($Site in $Sites)  
 {  
 foreach($Web in $Site.AllWebs)  
 {  
   Write-host -f Yellow "Processing Site: "$Web.URL  
   $ListCollection = $web.lists | Where-Object  { ($_.hidden -eq $false) -and ($_.IsSiteAssetsLibrary -eq $false)}  
   forEach ($List in $ListCollection)  
          {  
  
             $SiteData = New-Object PSObject     
             $SiteData | Add-Member -type NoteProperty -name "Web Application URL" -value $WebAppUrl    
             $SiteData | Add-Member -type NoteProperty -name "Web Application Name" -value $WebAppName     
             $SiteData | Add-Member -type NoteProperty -name "Site URL" -value $Site.Url  
                        $SiteData | Add-Member -type NoteProperty -name "Site Name" -value $Web.Title        
             $SiteData | Add-Member -type NoteProperty -name "List Name" -value $List.Title  
              
     $SiteDataCollection += $SiteData   
          }  
 }  
 }  
}  
  
#Export to excel  
$SiteDataCollection | Export-Csv -Path $Output -Force    
    
Write-Host "Successfully Completed" -ForegroundColor Red  

Then, you can set filter conditions to display the items you wanted

Thanks,
Echo Du

==================
Updated Answer =================

HI @sns ,

Get Inventory for All Site collections and all Lists in a Web Application.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue  
   
#Set Parameters  
$WebAppURL="http://sp"  
$ReportOutput="C:\temp\SiteInventory.csv"  
   
#Delete the Output report file if exists  
If (Test-Path $ReportOutput) { Remove-Item $ReportOutput }  
    
#Get all site collections from the web application  
Get-SPWebApplication $WebAppURL | Get-SPSite -Limit ALL | Get-SPWeb -Limit ALL -PipelineVariable Web | ForEach-Object {  
    Write-host -f Yellow "Processing Site: "$Web.URL  
     
    #Get all lists - Exclude Hidden System lists  
    $ListCollection = $Web.lists | Where-Object  { ($_.hidden -eq $false) -and ($_.IsSiteAssetsLibrary -eq $false)}  
       
    $ResultData = @()  
    #Iterate through All lists and Libraries  
    ForEach ($List in $ListCollection)  
    {  
            $ResultData+= New-Object PSObject -Property @{  
            'Site Title' = $Web.Title  
            'Site URL' = $Web.URL  
            'List/Library Name' = $List.Title  
            'Item Count' = $List.ItemCount  
            'Created By' = $List.Author.DisplayName  
            'Last Modified' = $List.LastItemModifiedDate.ToString();  
            'List URL' = "$($Web.Url)/$($List.RootFolder.Url)"  
            }  
    }  
   
    #Append data to CSV  
    $ResultData | Export-Csv $ReportOutput -NoTypeInformation -Append  
}  
    
Write-host -f Green "Report Generated Successfully at : "$ReportOutput  

Thanks,
Echo Du

—————————————————————— Updated Answer ——————————————————————

Hi @sns ,

As cascading drop down column is not a SharePoint list column type indeed.

Cascading drop down column is actually a choice or look up column, you can refer to the below PowerShell script to get all lists contains a choice/look up column in your farm.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue  
   
#Set Parameters  
$WebAppURL="http://sp"  
$ReportOutput="C:\temp\SiteInventory1.csv"  
  
$ResultData = @()  
  
#Delete the Output report file if exists  
If (Test-Path $ReportOutput) { Remove-Item $ReportOutput }  
    
#Get all site collections from the web application  
Get-SPWebApplication $WebAppURL | Get-SPSite -Limit ALL | Get-SPWeb -Limit ALL -PipelineVariable Web | ForEach-Object {  
    Write-host -f Yellow "Processing Site: "$Web.URL  
     
    #Get all lists - Exclude Hidden System lists  
    $ListCollection = $Web.lists | Where-Object  { ($_.hidden -eq $false) -and ($_.IsSiteAssetsLibrary -eq $false)}  
       
    #Iterate through All lists and Libraries  
    ForEach ($List in $ListCollection)  
    {  
  
            if( $List.Fields | where-object { $_.TypeDisplayName -eq "Lookup" -and $_.Hidden -eq $false -and $_.FromBaseType -eq $false } )  
            {  
          
                $ResultData+= New-Object PSObject -Property @{  
                'Site Title' = $Web.Title  
                'Site URL' = $Web.URL  
                'List/Library Name' = $List.Title  
                'Item Count' = $List.ItemCount  
                'Created By' = $List.Author.DisplayName  
                'Last Modified' = $List.LastItemModifiedDate.ToString();  
                'List URL' = "$($Web.Url)/$($List.RootFolder.Url)"  
                }  
            }  
    }  
}  
  
#Append data to CSV  
$ResultData | Export-Csv $ReportOutput -NoTypeInformation -Append   
  
Write-host -f Green "Report Generated Successfully at : "$ReportOutput  

Thanks,
Echo Du

—————————————— Updated Answer ————————————

Hi @sns ,

Did you create a “cascading dropdown list” type of Column types in your environment?

If this is the case, please refer to the following code:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue  
   
#Set Parameters  
$WebAppURL="http://sp"  
$ReportOutput="C:\temp\SiteInventory1.csv"  
  
$ResultData = @()  
  
#Delete the Output report file if exists  
If (Test-Path $ReportOutput) { Remove-Item $ReportOutput }  
    
#Get all site collections from the web application  
Get-SPWebApplication $WebAppURL | Get-SPSite -Limit ALL | Get-SPWeb -Limit ALL -PipelineVariable Web | ForEach-Object {  
    Write-host -f Yellow "Processing Site: "$Web.URL  
     
    #Get all lists - Exclude Hidden System lists  
    $ListCollection = $Web.lists | Where-Object  { ($_.hidden -eq $false) -and ($_.IsSiteAssetsLibrary -eq $false)}  
       
    #Iterate through All lists and Libraries  
    ForEach ($List in $ListCollection)  
    {  
  
            if( $List.Fields | where-object { $_.TypeDisplayName -eq "Cascading Drop Down List"} )  
            {  
          
                $ResultData+= New-Object PSObject -Property @{  
                'Site Title' = $Web.Title  
                'Site URL' = $Web.URL  
                'List/Library Name' = $List.Title  
                'Item Count' = $List.ItemCount  
                'Created By' = $List.Author.DisplayName  
                'Last Modified' = $List.LastItemModifiedDate.ToString();  
                'List URL' = "$($Web.Url)/$($List.RootFolder.Url)"  
                }  
            }  
    }  
}  
  
#Append data to CSV  
$ResultData | Export-Csv $ReportOutput -NoTypeInformation -Append   
  
Write-host -f Green "Report Generated Successfully at : "$ReportOutput  

Thanks,
Echo Du

=================

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi Echo Du,

Can you please tell me where I should supply webapplication URL? and how to give?
if you can show me it would be great thank you