Listing All BCS in Use with PowerShell

Background Info

You wish to scan your SharePoint and figure out the list of all BCS (External Content Types) that are used across a given SharePoint Web Application. A BCS can be used by an External List or by an External Data field on any given list or document library. Using PowerShell, will we develop a script that takes in the URL of a specific Web Application, loops through all of its site collections and web to find all external lists, and then opens every other lists and libraries and checks for external data fields. If ever an external data field is encountered, we will need to extract the name of the BCS that is associated with it.

 

Script

The PowerShell script we will declare will be calling a function called Dig that receives the URL of a web as a parameter, and then recursively calls itself on all of its sub-webs. This method will loop through all lists of the web located at the URL received, and will do two things: print the name of external lists encountered in green, and print the BCS associated with any external data field encountered in yellow. Remember that the goal of our script is not to find the name of lists that have external data fields, but to find out what BCS are being used. You would normally run this script to gather a list of all BCS in use and then remove all other BCS to do of hell of a spring cleaning. Enjoy!

 

 

Add-PSSnapin Microsoft.SharePoint.PowerShell 

function Dig($url)

{

     $rootWeb = Get-SPWeb $url

     foreach($web in $rootWeb.Webs)

     {

           $externalLists = $web.Lists 

           foreach($list in $externalLists)

           {

                try

                {

                     if($list -ne $null)

                     {

                           if($list.BaseTemplate -eq “ExternalList”)

                           {

                                $ds = $list.DataSource

                                $report += $web.Url + ” – {” + $list.Title + “} – [” + $ds.GetProperty(“LobSystemInstance”) + “]”

                                Write-Host $list.Title -BackgroundColor “Green” -ForegroundColor “Black”

                           }

                           else

                           {                                   

                                $fields = $list.Fields

                                foreach($field in $fields)

                                {

                                     if($field.TypeDisplayName -eq “External Data”)

                                     {                              

                                           $ef = [Microsoft.SharePoint.SPBusinessDataField]($field)                                   

                                           Write-Host $ef.EntityName -Backgroundcolor “Yellow” -ForegroundColor “Black”              

                                     }

                                }   

                           }

                     }

                }

                catch

                {

                     Write-host $list.Title -BackgroundColor “Red”

                }

           }   

           Dig($web.Url)    

           $web.Dispose()

     }

}

 

$url = Read-Host “URL of Web Application” 

$report = “” 

$webApp = Get-SPWebApplication $url 

foreach($site in $webApp.Sites)

{   

     $site.RootWeb.Url

     Dig($site.RootWeb.Url)

     $site.Dispose()

}

$report | Out-File c:\temp\BCSReport.txt​