This blog post provides sample Windows PowerShell® scripts that displays information on external lists in your Microsoft SharePoint® on-premises environments and the external content types (ECTs) used by those external lists. Only those sites that you have access to, will appear in the output. This blog post details:
- Finding all external lists in one site
- Finding all external lists in a site collection or a web application
- Listing External Lists by External Content Types
Tip: For information on the permissions needed to run SharePoint Windows PowerShell cmdlets in an on-premises installation, see the blog post: When is a SharePoint Farm Administrator not a SharePoint Farm Administrator?
Find all external lists in one site
To find all external lists in a site is as easy as typing either of the following commands, where http://intranet/sites/lt/bcsdemo is the URL of the site:
(Get-SPWeb http://intranet/sites/lt/bcsdemo).Lists |
where {$_.HasExternalDataSource -eq $true} |
Select Title
Or
Get-SPWeb http://intranet/sites/lt/bcsdemo |
Select -ExpandProperty Lists |
where {$_.HasExternalDataSource -eq $true} |
Select Title
To quickly display a count of the number of external lists in a site, type:
((Get-SPWeb http://intranet/sites/lt/bcsdemo).Lists |
where {$_.HasExternalDataSource -eq $true}).Count
However if you want to find the external content type (ECT) associate with the external list, type commands similar to the following:
# Use your site in place of http://intranet/lt/bcsdemo
$web = Get-SPWeb http://intranet/sites/lt/bcsdemo
Find all the lists in the site and save them in a variable $lists
$lists = $web.lists
# For each external list output the list name, ECT name and namespace
$lists | where {$_.HasExternalDataSource -eq $true } |
foreach {
$_.Title,
$_.DataSource.GetEntity().Name,
$_.DataSource.GetEntity().Namespace
$_.DataSource.GetEntity().Version
}
Note: An ECT is uniquely identified by its name, namespace and version number.
The output from the above script is not easy to read. Using the technique described in the TechNet article: Windows PowerShell Tip of the Week the output can be displayed in a table, using the following script:
# Create custom table formatting
$a = @{Expression={$_.Title};Label=”List”;width=20},
@{Expression={$_.DataSource.GetEntity().Name};Label=”ECT”;width=20},
@{Expression={$_.DataSource.GetEntity().Namespace};Label=”Namespace”;width=25},
@{Expression={$_.DataSource.GetEntity().Version};Label=”Version”;width=15}
# Display list name, ECT name and namespace as a table
# This line of code assume you have initiated the $lists variable as
# detailed in the first snippet of code
$Lists | where {$_.HasExternalDataSource -eq $true } | Format-Table $a
The output from the command will look similar to:
List ECT Namespace Version
—- — ——— ——-
BigExtList BigECT http://intranet.dp.loc… 1.5.0.0
matttest Products Northwind_MetaMan 1.0.0.2
Northwind Customers Customers http://intranet.dp.loc… 1.14.0.0
Northwind Employees Northwind_Employees Northwind 1.0.0.0
Orders Orders http://intranet.dp.loc… 1.3.0.0
Products Products http://intranet.dp.loc… 1.0.0.0
Suppliers Supp|liers Northwind_MetaMan 1.0.0.1
Tip: If you do not know the width for the table columns, then use the –AutoSize parameter on the Format-Table command.
The GetEntity method on the DataSource field is of type, Microsoft.SharePoint.BusinessData.MetadataModel.Static.Entity, and makes a call to SharePoint to obtain the ECT information from the Business Data Connectivity (BDC) Metadata store. When an ECT is deleted and external lists based on that ECT are not, then a call to the GetEntity method will result in an error message.
You can use the GetEntity method to obtain other useful information, such as, GetActions() and GetAccessControlList(). However, some of this information is saved as a property of the list object, therefore, if you have saved the reference to the list object in a variable, it will be more efficient to display the information using the variable.
Warning: As the code in this post uses a variable to store SPWeb you should dispose of the memory assigned to the variable. This is true for SPSite and SPSiteAdministration objects; therefore when using code that use these objects you should look to use the two SharePoint cmdlets Start-SPAssignment and Stop-SPAssignment.
Find all external lists in a site collection or a web application
To find all external lists, in a site collection type a command similar to the following:
Get-SPSite http://intranet/sites/lt -Limit All |
Select -ExpandProperty AllWebs | Select -ExpandProperty Lists |
where {$_.hasexternaldatasource -eq $true} | select ParentWebUrl, Title
Which has output similar to:
ParentWebUrl Title
———— —–
/sites/LT Access Assets Ext List
/sites/LT Access Retail Sales Ext List
/sites/LT/bcs2013demo BigExtList
/sites/LT/bcs2013demo mattes
/sites/LT/bcs2013demo Northwind Customers
/sites/LT/bcs2013demo Northwind Employees
/sites/LT/bcs2013demo Orders
/sites/LT/bcs2013demo Products
/sites/LT/bcs2013demo Suppliers
/sites/LT/bcshostsite Customers
/sites/LT/bcshostsite Orders
/sites/LT/dvwp2013/pacebus PB Customers
Similarly, to find all external lists in all web application type the following command:
Get-SPWebApplication | Select –ExpandProperty Sites |
Select -ExpandProperty AllWebs | Select -ExpandProperty Lists |
where {$_.hasexternaldatasource -eq $true} | select ParentWebUrl, Title
For a summary report that totals the number of ECT’s per site, type:
Get-SPWebApplication | Select -ExpandProperty Sites |
select -ExpandProperty AllWebs | select -ExpandProperty Lists |
where {$_.hasexternaldatasource -eq $true} | group parentweburl
Which has output similar to:
Count Name Group
—– —- —–
2 /sites/LT {Access Assets Ext List, Access Retail…
7 /sites/LT/bcs2013demo {BigExtList, matttest, Northwind Custome…
2 /sites/LT/bcshostsite {Customers, Orders}
1 /sites/LT/dvwp2013/pac… {PB Customers}
Warning: Windows PowerShell scripts can affect the performance of SharePoint and therefore can impact your users. You should consider running scripts outside of business hours.
List External Lists by External Content Types
When you use many external content types (ECTs) to find the total number of external lists that uses external content types, then you can type a command similar to:
# Save in a variable the external lists you wish to report on
# in this example, the variable contains all external lists
# in a specific web application
$Lists = Get-SPWebApplication http://intranet.dp.local |
Select -ExpandProperty Sites | Select -ExpandProperty AllWebs |
select -ExpandProperty Lists | where {$_.hasexternaldatasource -eq $true}
# Save the expression to obtain the ECT name in a variable
$e=@{Expression={$_.DataSource.GetEntity().Name}}
# Output external lists grouped by ECT
$Lists | Sort $e | Format-Table Title, ParentWebUrl –Groupby $e
Which has output similar to:
$_.DataSource.GetEntity().Name: BigECT
Title ParentWebUrl
—– ————
BigExtList /sites/LT/bcs2013demo
$_.DataSource.GetEntity().Name: Customers
Title ParentWebUrl
—– ————
Customers /sites/LT/bcshostsite
Northwind Customers /sites/LT/bcs2013demo
Note: When no $_.DataSource.GetEntity().Name: <external content type name> is displayed prior to displaying the external list details, then the ECT associated with the external list has been deleted.
To output a table that counts the number of external lists created for each ECT then type the following command:
$lists | sort $e | group $e
Which has output similar to the following, where the Name column displays the ECT name, and the Group column contains the name of each external list created from the ECT.
Count Name Group
—– —- —–
1 BigECT {BigExtList}
2 Customers {Customers, Northwind Customers}
1 Northwind_Employees {Northwind Employees}
2 Products {Products, matttest}
1 Suppliers {Suppliers}
Tip: If you are a new SharePoint Farm Administrator and new to Windows PowerShell then you may want to read the Chapter written by Penelope Coventry from the Microsoft SharePoint 2010 Administrator’s Companion, Microsoft Press, August 2010, which is still relevant for SharePoint 2013, and can be found at: http://technet.microsoft.com/en-us/library/gg550867.aspx.
