Skip Ribbon Commands
Skip to main content

Blog

:

SharePoint Designer > Blog > Posts > Finding External Lists using Windows PowerShell®
 

 Posts

 
April 06
Finding External Lists using Windows PowerShell®

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:

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.GenEntity().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  Suppliers 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.

Go to top of post.

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.

Go to top of post.

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.

Go to top of post.

Add Comment

Items on this list require content approval. Your submission will not appear in public views until approved by someone with proper rights. More information on content approval.

Title


Body *


Comment Date *

Select a date from the calendar.
Please enter today's date so I know you are a real person.

Attachments