The script below does all that. Here is an overview first:
- Iterates over all lists and libraries in all Webs in all Site Collections in a SharePoint Online tenant (excluding the Admin Portal and My Sites) to find a column that matches the SCName variable. If found it outputs the details of that occurrence of the column. If the DeleteColumns variable is set to $true the column will automatically be deleted or in the case where the PromptBeforeDelete variable is also set to $true then you are prompted to optionally delete the column where it was found.
- Input variables:
- $script:SCName: string representing a Site Column's display name, internal name OR GUID
- $tenantUrl: Tenant's root SharePoint site URL (a string)
- $AdminUrl: Tenant's Admin Portal URL (a string)
- $script:Debug: show debug or other non-critical error messages ($false or $true)
- $script:DeleteColumns: delete the column from lists or libraries ($false or $true)
- $script:PromptBeforeDelete: ask whether to delete the column each time it is found ($false or $true)
- $user: account used to log in to the tenant admin portal and all site collections
- $spocmdletsPath: local file system path to the folder where the spocmdlets were installed
- $loadcsompropertiesPath: local file system path to the folder where the loadl-csomproperties script is located
- Output: list of SharePoint lists or libraries where a column was found that matched the input string. Output list returns the following details for each column found:
- Site URL
- List/Library Name
- Column's display name
- Column's internal name
- Column's GUID
- Requirements/Dependencies
- CSOM dlls:
- Microsoft.SharePoint.Client.dll
- Microsoft.SharePoint.Client.Runtime.dll
- SharePoint Online Automation Cmdlets: https://sharepointonlineautomationcmdlets.codeplex.com
- Load-CSOMProperties.ps1: https://gist.github.com/glapointe/cc75574a1d4a225f401b
- Account used to authenticate against SharePoint Online must have the following permissions:
- SharePoint Administrator Role for the tenant
- At least Design rights on all Lists & Libraries in all Webs in all Site Collections for the script to search
- Future enhancements
- Output to CSV
- Create as a function with parameters or parameterized ps1
- Download and load dependencies on the fly as needed
#Configurable Settings
$user = "adminuser@tenanturl.com" #This user will be used to connect to site and create library. Pwd will be prompted for during execution.
$spocmdletsPath = "C:\downloads\SPO-Powershell\SPOAutomationCmdlets\SPOCmdlets" #
$loadcsompropertiesPath = "C:\downloads\SPScripts\Load-CSOMProperties.ps1"
$AdminUrl = "https://tenant-admin.sharepoint.com/"
$tenantUrl ="https://tenant.sharepoint.com"
$script:SCName = "Coffee"
$script:Debug = $false
$script:DeleteColumns = $false
$script:PromptBeforeDelete = $true
#Import-Module SPOCmdlets needed for Get-SPOContext, New-SPOList & Add-SPOListCT cmdlets
Import-Module $spocmdletsPath
Add-Type –Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type –Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
$loadcsompropertiesPath
function ConvertFrom-SecureToPlain {
param( [Parameter(Mandatory=$true)][System.Security.SecureString] $SecurePassword)
# Create a "password pointer"
$PasswordPointer = [Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecurePassword)
# Get the plain text version of the password
$PlainTextPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto($PasswordPointer)
# Free the pointer
[Runtime.InteropServices.Marshal]::ZeroFreeBSTR($PasswordPointer)
# Return the plain text password
#[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($PlainTextPassword))
$PlainTextPassword
}
$SecureString = $null
$r = $null
$SecureString = Read-Host -AsSecureString 'Enter password for'$user
$r = ConvertFrom-SecureToPlain($SecureString)
$SecurePassword = $r | ConvertTo-SecureString -AsPlainText -Force
$script:Credentials = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $user, $SecurePassword
Connect-SPOService -Url $AdminUrl -Credential $Credentials
#Get all non admin or My or Public site collections
$sites = Get-SPOSite | where {$_.url -Match "$tenantUrl/*"}
Disconnect-SPOService
$:Credentials = New-Object -TypeName Microsoft.SharePoint.Client.SharePointOnlineCredentials -argumentlist $user, $SecurePassword
[System.Collections.ArrayList]$libraries = New-Object System.Collections.ArrayList
function script:GetListsThatUseSC($web,$libraries){
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($web.url)
$Context.Credentials = $Credentials
$context.RequestTimeOut = 5000 * 60 * 10;
$webI = $Context.Web
#$context.Load($webI)
Load-CSOMProperties -object $webI -propertyNames @("Title", "Url", "Webs")
$Lists = $webI.Lists
$Context.Load($Lists)
try {
$Context.ExecuteQuery()
#Load-CSOMProperties -object $Lists -propertyNames @("AllProperties", "Url", "Title")
Load-CSOMProperties -parentObject $webI -collectionObject $webI.Lists -propertyNames @("Id", "Title", "Fields") -parentPropertyName "Lists"
$Context.ExecuteQuery()
if($Debug)
{
Write-Progress -Activity $webI.url -PercentComplete 100 #"("$webI.Lists.Count "lists)" #-foregroundcolor black -backgroundcolor green -NoNewLine
}
#iterate over each list/library
[int]$listIndex = 1
[int]$listTotal = [convert]::ToInt32($Lists.Count,10)
foreach( $list in $Lists )
{
[int]$percComplete = ($listIndex/$listTotal)*100
Write-Progress -Activity $webI.url -PercentComplete $percComplete
#$libraries.Add($list.Title)
foreach($field in $list.Fields)
{
if (($field.Title -eq $SCName) -or ($field.InternalName -eq $SCName) -or ($field.Id -eq $SCName))
{
Write-Host $web.Url">"$list.Title":"$field.Title","$field.Id","$field.InternalName #-foregroundcolor black -backgroundcolor green
#Delete the column?
if($DeleteColumns)
{
if($PromptBeforeDelete){
$d = Read-Host "Delete column? [Y]es or [N]o"
if($d.ToLower() -eq "y")
{
$Field.DeleteObject()
$Context.ExecuteQuery()
}
}else
{
$Field.DeleteObject()
$Context.ExecuteQuery()
}
}
}
}
$listIndex = $listIndex+1
}
}
catch {
if($Debug)
{
Write-Host "Not able to authenticate to SharePoint Online $_.Exception.Message" -foregroundcolor black -backgroundcolor Red
}
}
#iterate over all columns
#log library and lists/library URL where site column is used
#iterate over each subweb
try{
if($webI.Webs.Count > 0)
{
foreach($sub in $webI.Webs)
{
GetListsThatUseSC($sub, $libraries)
}
}
}catch{
if($Debug)
{
Write-Host "Subwebs not found or could not be loaded: $_.Exception.Message" -foregroundcolor black -backgroundcolor Red
}
}
return $libraries
}
#iterate of each Site Collection
foreach ($site in $sites)
{
GetListsThatUseSC($site, $libraries)
}
#$libraries
#clear pwd
$r = $null
