Introduction


Ever wondered how many fields are referencing a Managed Metadata Termset? It's going to be a long and boring job using the Web UI to click through every web... and every list in every web... and every field in every list, looking for all the fields referencing a particular termset. Just writing that in a sentence was long enough!

This is the sort of job where PowerShell really shines!

The example below demonstrates creating a script (with a number of functions) to recurse through a site collection, creating a report of all the fields using a termset.

Applies To

  • SharePoint 2010
  • SharePoint 2013

Download


Walkthrough


The basic PowerShell used to check if a Managed Metadata field is using a particular Managed Metadata Termset is:

$termSetId = "e07cab2f-ef85-473e-a4a7-1104b5daf192"            
$field = (Get-SPWeb "http://mdysp13").Lists["Documents"].Fields["Country"]            
if($field.GetType().Name -eq "TaxonomyField"){            
 if($field.TermSetId.ToString() -eq $termSetId){            
  Write-Host "Houston, we have a match!" -foregroundcolor darkyellow;            
 }            
}
First the Managed Metadata Termset ID is stored in a variable (more about getting a termsets ID later). Then the  field is checked to see if it's a Taxonomy Field. Finally, the fields TermSetId property is compared to the Termset ID stored, to see if they match.

The PowerShell can be extended to check a collection of fields:

$fieldCollection = (Get-SPWeb "http://mdysp13").Lists["Documents"].Fields            
$termSetId = "e07cab2f-ef85-473e-a4a7-1104b5daf192"            
foreach($field in $fieldCollection)            
{            
 if($field.GetType().Name -ne "TaxonomyField"){            
  continue;            
 }            
 if($field.TermSetId.ToString() -ne $termSetId){            
  continue;            
 }            
 #if we get to here, we have a match!            
}

To get the Taxonomy TermSet ID, you can use the Get-SPTaxonomySession cmdlet for the current site, and pipe it to Format-Table (FT), to list all of the Termsets.

$w = Get-SPWeb "http://mdysp13";                        
$tsession = Get-SPTaxonomySession -Site $w.Site;                        
$tsession.GetTermSets("Countries",1033) | FT Name,ID
#Or, if you want to get a term set based on the SPWeb's default language ID            
$tsession.GetTermSets("Countries",$w.Language) | FT Name,ID

Pretty cool huh?

If you want to get a list of all the termsets, then you can write a simple function to return all the termsets as a list.

function List-AllTermSets{            
 [CmdletBinding()]            
  Param(             
    [parameter(Mandatory=$true, ValueFromPipeline=$true)][Microsoft.SharePoint.SPWeb]$web            
   )            
 $termSetInfo = New-Object psobject            
 $termSetInfo | Add-Member -MemberType NoteProperty -Name "Store" -value ""
 $termSetInfo | Add-Member -MemberType NoteProperty -Name "StoreId" -value ""
 $termSetInfo | Add-Member -MemberType NoteProperty -Name "Group" -value ""
 $termSetInfo | Add-Member -MemberType NoteProperty -Name "GroupId" -value ""
 $termSetInfo | Add-Member -MemberType NoteProperty -Name "TermSet" -value ""
 $termSetInfo | Add-Member -MemberType NoteProperty -Name "TermSetId" -value ""
             
 $tsession = Get-SPTaxonomySession -Site $web.Site;            
 $tstores =  $tsession.TermStores;             
 $list = @();            
 foreach($tstore in $tstores)            
 {            
  $tgroups = $tstore.Groups;            
  foreach($tgroup in $tgroups)            
  {            
   $tsets = $tgroup.TermSets;            
   foreach($tset in $tsets)            
   {            
    $tinfo = $null;            
    $tinfo = $termSetInfo | Select-Object *;            
    $tinfo.Store = $tstore.Name;            
    $tinfo.StoreId = $tstore.ID;            
    $tinfo.Group = $tgroup.Name;            
    $tinfo.GroupId = $tgroup.ID;            
    $tinfo.TermSet = $tSet.Name;            
    $tinfo.TermSetId = $tSet.ID;            
    $list += $tinfo;            
   }            
  }             
 }            
 return $list;            
}

So, what if you wanted all of this scripted? A function you can call that generates a report. Well, prepare to roll up your sleeves and poise your fingers over the Ctrl+C key combo!

We need a couple of functions for this, performing the following tasks;

1. A function to get a list of all the taxonomy (managed metadata) fields in a field collection referencing a termset
2. A function to call which will report on all the taxonomy (managed metadata) fields in the web, the webs lists, and the webs sub webs, that are referencing a given termset.

Each function is outlined below.

Note: If you'd rather download the script, download it from the Microsoft TechNet Gallery here: Find all SPFields that are using a Managed Metadata TermSet

1. Get a list of all the fields (in a field collection) using a termset

function Get-FieldsUsingTermSet            
{            
 [CmdletBinding()]            
  Param(             
    [parameter(Mandatory=$true, ValueFromPipeline=$true, Position=1)][Microsoft.SharePoint.SPFieldCollection]$fieldCollection,            
    [parameter(Mandatory=$true, Position=2)][Microsoft.SharePoint.Taxonomy.TermSet]$TermSet            
   )            
 $MetadataField = New-Object psobject            
 $MetadataField | Add-Member -MemberType NoteProperty -Name "ParentListUrl" -value ""
 $MetadataField | Add-Member -MemberType NoteProperty -Name "ParentListTitle" -value ""
 $MetadataField | Add-Member -MemberType NoteProperty -Name "FieldTitle" -value ""
 $MetadataField | Add-Member -MemberType NoteProperty -Name "FieldId" -value ""            
             
 $matches = @();            
 foreach($field in $fieldCollection)            
 {            
  if($field.GetType().Name -ne "TaxonomyField"){            
   continue;            
  }            
  if($field.TermSetId.ToString() -ne $TermSet.Id.ToString()){continue;}            
  $tf = $MetadataField | Select-Object *;            
  $tf.ParentListUrl = $field.ParentList.ParentWeb.Url;            
  $tf.ParentListTitle = $field.ParentList.Title;            
  $tf.FieldTitle = $field.Title;            
  $tf.FieldId = $field.ID;            
  $matches += $tf;            
 }            
 return $matches;            
}

2. A parent function to bring it together, that will give you some options (like recursively checking the web,  searching just web level fields)

function Get-ManagedMetadataFieldUses            
{            
 [CmdletBinding()]            
  Param(             
    [parameter(Mandatory=$true, ValueFromPipeline=$true, Position=1)][Microsoft.SharePoint.SPWeb]$web,            
    [parameter(Mandatory=$true, Position=2)][Microsoft.SharePoint.Taxonomy.TermSet]$TermSet,
    [parameter(Mandatory=$false, Position=4)][switch]$Recurse,            
    [parameter(Mandatory=$false, Position=5)][switch]$WebLevelFieldsOnly            
   )             
             
 $matches = @();             
 $matches += Get-FieldsUsingTermSet $web.Fields $TermSet;            
             
 if($WebLevelFieldsOnly -eq $false)            
 {            
  foreach($list in $web.Lists)            
  {            
   $matches += Get-FieldsUsingTermSet $list.Fields $TermSet            
  }            
 }            
             
 if($Recurse)            
 {            
  foreach($subweb in $web.Webs)            
  {            
   $matches += Get-ManagedMetadataFieldUses $subweb $TermSet $Recurse $WebLevelFieldsOnly;            
  }            
 }            
             
 return $matches            
}

Examples of using the script to create some reports.

1. Download the script from here: Find all SPFields that are using a Managed Metadata TermSet
2. Save the script somewhere. "C:\Temp" is a good place!
3. If you haven't already, set the PowerShell execution policy to Bypass (this will allow you to import all PowerShell scripts, but be aware of the security implications)

Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope CurrentUser

4. Import the script into PowerShell.

Import-Module C:\Temp\Get-ManagedMetadataFieldUses.ps1

5. Run a few commands to get a termset to report on. In this example, we get a termset called "Countries"

#Get the SPWeb object            
$w = Get-SPWeb http://mdysp13;            
#Get the taxonomy session used by the SPWeb's site            
$tsession = Get-SPTaxonomySession -Site $w.Site;            
#Get all the TermSets with the name "Countries", and the web's default Language ID
$termSets = $tsession.GetTermSets("Countries",$w.Language)            
#Display the TermSets found            
$termSets | FT @{Label="Group";Expression={($_.Group).Name}},Name,ID            
#Select the first TermSet            
$termSet = $termSets[0]



6. Call the Get-ManagedMetadataFieldUses function, and store the results in the $matchingFields variable.

$matchingFields = Get-ManagedMetadataFieldUses -web $w -TermSet $termSet -Recurse

Now do some reporting!!

Display all of the results in the raw format.

$matchingFields | FT



Display all of the results, grouping them by the Site. This view of the data will show you how many fields in each site (or web) are referencing the termset)

$matchingFields | Group-Object ParentListUrl



This improves on the previous command, displaying all of the results, grouping them by the Site. In this view, all the fields are listed, grouped under the site they belong to.

$matchingFields | Group-Object ParentListUrl | Select -ExpandProperty Group  | Format-Table -GroupBy ParentListUrl



Group the objects into a Hash Table. This will allow you to directly reference a web URL, to a get a list of fields in that web that reference the termset.

$hashTable = $matchingFields | Group-Object ParentListUrl -AsHashTable -AsString
$hashTable."http://mdysp13" | FT ParentListTitle,FieldTitle,FieldId -AutoSize



Finally, you can export your results to a CSV file for further analysis!

$matchingFields | Export-CSV  -Path C:\temp\fieldreport.csv -NoTypeInformation -Delimiter "`t"

Download the full script from the Microsoft TechNet Gallery here: Find all SPFields that are using a Managed Metadata TermSet

References