SharePoint 20XX: Script PowerShell to export in CSV all the SPLists from a SPFarm

Based on a script created by Benoit Jester To export in CSV all the Sites and Subsites from a SPFarm:
SharePoint 20xx : Script PowerShell permettant de lister les sites peu utilisés
I created a new PowerShell script you have just to adapt (only 2 parameters) and execute (or schedule) to have the CSV you can open to check the content you have into your SPFarm.
param( 
    [Parameter(Mandatory=$False)] 
     [bool]$displayMessages = $true, 
    [Parameter(Mandatory=$False)] 
     [string]$SMTPServer="", 
    [Parameter(Mandatory=$False)] 
     [string]$ToEmailAddress="", 
    [Parameter(Mandatory=$False)] 
     [string]$FromEmailAddress="", 
    [Parameter(Mandatory=$False)] 
     [string]$delimiter="|" 
)

[array]$ExceptionListnames = "List Template Gallery", "Master Page Gallery", "Site Template Gallery", "Web Part Gallery", "User Information List", "Settings", "Galería de plantillas de sitios", "Galería de plantillas de listas", "Galerie de modèles de sites", "Galerie de modèles de listes"

[string]$FarmType = "YOUR INTRANET TEAMSITES" 
[string]$FolderToPutCSVName = "C:\TEMP\" 
[string]$CSVFileName = "Lists-Usage-YourTeamsites.CSV" 
[string]$ColorToShow = "green" 
[string]$MyListIndexedColumns = "" 
$pattern = '[^a-zA-Z]' 
[string]$ColumnTitleCleaned = ""

# Applies Read Access to the specified accounts for a web application 
Function Add-UserPolicy([String]$url) 
{ 
    Try 
    { 
        $webapp = [Microsoft.SharePoint.Administration.SPWebApplication]::Lookup("$url") 
        $user = ([Security.Principal.WindowsIdentity]::GetCurrent()).Name 
        $displayName = "Sites Usage Read Account" 
        $perm = "Full Read" 
        
        # If the web app is not Central Administration 
        If ($webapp.IsAdministrationWebApplication -eq $false) 
        { 
            # If the web app is using Claims auth, change the user accounts to the proper syntax 
            If ($webapp.UseClaimsAuthentication -eq $true) 
            {$user = 'i:0#.w|'+$user} 
            [Microsoft.SharePoint.Administration.SPPolicyCollection]$policies = $webapp.Policies 
            [Microsoft.SharePoint.Administration.SPPolicy]$policy = $policies.Add($user, $displayName) 
            [Microsoft.SharePoint.Administration.SPPolicyRole]$policyRole = $webapp.PolicyRoles | where {$_.Name -eq $perm} 
            If ($policyRole -ne $null) 
            {$policy.PolicyRoleBindings.Add($policyRole)} 
            $webapp.Update() 
            If($displayMessages) 
            {Write-Host -ForegroundColor White " Read access applied for `"$user`" account to `"$url`""} 
        } 
    } 
    Catch 
    { 
        $_ 
        Write-Warning "An error occurred applying Read access for `"$user`" account to `"$url`"" 
    } 
}

# Load assemblies 
Function Load-Assemblies 
{ 
    Try 
    { 
        If ((Get-PsSnapin |?{$_.Name -eq "Microsoft.SharePoint.PowerShell"})-eq $null) 
        { 
            If($displayMessages) 
            { 
                Write-Host -ForegroundColor Green "-----------------------------------------" 
                Write-Host -ForegroundColor Green " - Loading SharePoint Powershell Snapin -" 
                Write-Host -ForegroundColor Green "-----------------------------------------" 
            } 
            Add-PsSnapin Microsoft.SharePoint.PowerShell -ErrorAction Stop | Out-Null 
        } 
    } 
    Catch 
    { 
        If($displayMessages) 
        { 
            Write-Host -ForegroundColor Green "------------------------------------------" 
            Write-Host -ForegroundColor Green " - Loading Microsoft.SharePoint Assembly -" 
            Write-Host -ForegroundColor Green "------------------------------------------" 
        } 
        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") | Out-Null 
    } 
}

# Send Email with log file as attachment 
Function SendEmail($attachment) 
{ 
    Try 
    { 
        If($displayMessages) 
        { 
            Write-Host -ForegroundColor White "--------------------------------------------------------------" 
            Write-Host -ForegroundColor White " Sending Email to $ToEmailAddress with $attachment in attachment." 
        } 
        Send-MailMessage -To $ToEmailAddress -From $FromEmailAddress -Subject "Sites Usage - $env:COMPUTERNAME" -SmtpServer $SMTPServer -Attachments $attachment 
        
        If($displayMessages) 
        {Write-Host -ForegroundColor Green " Email sent successfully to $ToEmailAddress"} 
    } 
    Catch 
    {Write-Warning $_} 
}

$DateStarted = $(Get-date)

If($displayMessages) 
{ 
    Write-Host -ForegroundColor Green "----------------------------------" 
    Write-Host -ForegroundColor Green "- Script started on $DateStarted -" 
    Write-Host -ForegroundColor Green "----------------------------------" 
} 
# Check Permission Level 
If (-NOT ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")) 
{Write-Warning "You don't have Administrator rights to run this script."} 
else 
{ 
    If($SMTPServer) 
    { 
        If (!$ToEmailAddress -or !$FromEmailAddress) 
        { 
            Write-Warning "Please specify a 'ToEmailAddress' and a 'FromEmailAddress' parameter." 
            Exit 
        } 
    } 
    # Load assemblies 
    Load-Assemblies

    # Local variables 
    $sitesList = $null 
    $sitesList = @()

    # Build structure 
    $itemStructure = New-Object psobject 
    $itemStructure | Add-Member -MemberType NoteProperty -Name "FarmType" -value "" 
    $itemStructure | Add-Member -MemberType NoteProperty -Name "SPWebAppURL" -value "" 
    $itemStructure | Add-Member -MemberType NoteProperty -Name "SPSiteCollURL" -value "" 
    $itemStructure | Add-Member -MemberType NoteProperty -Name "SPWebURL" -value "" 
    $itemStructure | Add-Member -MemberType NoteProperty -Name "ListTitle" -value "" 
    $itemStructure | Add-Member -MemberType NoteProperty -Name "ListGUID" -value "" 
    $itemStructure | Add-Member -MemberType NoteProperty -Name "ListCreated" -value "" 
    $itemStructure | Add-Member -MemberType NoteProperty -Name "ListLastModifiedDate" -value "" 
    $itemStructure | Add-Member -MemberType NoteProperty -Name "ListDefaultViewURL" -value "" 
    $itemStructure | Add-Member -MemberType NoteProperty -Name "ListTemplate" -value "" 
    $itemStructure | Add-Member -MemberType NoteProperty -Name "ListItemsNumber" -value "" 
    $itemStructure | Add-Member -MemberType NoteProperty -Name "ListIndexedColumns" -value "" 
    
    If($displayMessages) 
    { 
        Write-Host -ForegroundColor Green "-----------------------" 
        Write-Host -ForegroundColor Green " - Scanning All Sites -" 
        Write-Host -ForegroundColor Green "-----------------------" 
    } 
    
    # Browse sites 
    $WebSrv = [microsoft.sharepoint.administration.spwebservice]::ContentService 
    foreach ($webApp in $WebSrv.WebApplications) 
    { 
        foreach ($AltUrl in $webapp.AlternateUrls) 
        {Add-UserPolicy $AltUrl.uri} 
        
        foreach ($site in $WebApp.sites) 
        { 
            $rootweb = $site.RootWeb; 
            foreach($web in $site.AllWebs) 
            { 
                foreach($MyList in $web.lists) 
                { 
                    if($ExceptionListnames -notcontains $MyList.Title) 
                    { 
                        $MyListIndexedColumns = "" 
                        foreach($MySPField in $MyList.Fields) 
                        { 
                            if($MySPField.Indexed) 
                            { 
                                $ColumnTitleCleaned =  $MySPField.Title -replace $pattern, '' 
                                $MyListIndexedColumns += $ColumnTitleCleaned +"["+ $MySPField.InternalName +"]; " 
                            } 
                        }

                        if($MyList.ItemCount -gt 5000) 
                        { 
                            $ColorToShow = "red" 
                        } 
                        else 
                        { 
                            $ColorToShow = "green" 
                        } 
                        If($displayMessages) 
                        { 
                            Write-Host " -------------------------------------------------- " 
                            Write-Host "      List Name:", $MyList.Title, "- GUID:", $MyList.ID -foregroundcolor $ColorToShow 
                            Write-Host "      >>SPWebURL URL:", $web.Url -foregroundcolor $ColorToShow 
                            Write-Host "      >>Items Count:", $MyList.ItemCount -foregroundcolor $ColorToShow 
                            Write-Host "      >>Created Date:", $MyList.Created -foregroundcolor $ColorToShow 
                            Write-Host "      >>Last modified Date:", $MyList.LastItemModifiedDate -foregroundcolor $ColorToShow 
                            Write-Host "      >>List Indexed Columns:", $MyListIndexedColumns -foregroundcolor $ColorToShow 
                        } 
                        # Build structure 
                        $siteInfos = $itemStructure | Select-Object *;

                        $siteInfos.FarmType = $FarmType; 
                        $siteInfos.SPWebAppURL = $web.Url.Split("/")[2]; 
                        $siteInfos.SPSiteCollURL = $site.URL; 
                        $siteInfos.SPWebURL =  $web.Url;

                        $siteInfos.ListTitle = $MyList.Title; 
                        $siteInfos.ListGUID = $MyList.ID; 
                        $siteInfos.ListCreated = $MyList.Created.ToString('d'); 
                        $siteInfos.ListLastModifiedDate = $MyList.LastItemModifiedDate.ToString('d'); 
                        $siteInfos.ListDefaultViewURL = $MyList.DefaultViewUrl; 
                        $siteInfos.ListTemplate = $MyList.BaseTemplate; 
                        $siteInfos.ListItemsNumber = $MyList.ItemCount; 
                        $siteInfos.ListIndexedColumns = $MyListIndexedColumns; 
                        
                        $sitesList += $siteInfos; 
                    } 
                } 
            } 
        } 
        $rootweb.Dispose()

    } 
    
    # CSV Export 
    If($displayMessages) 
    { 
        Write-Host -ForegroundColor Green "---------------" 
        Write-Host -ForegroundColor Green " - CSV Export -" 
        Write-Host -ForegroundColor Green "---------------" 
    } 
    $sitesList | Where-Object {$_} | Export-Csv -Delimiter "$delimiter" -Path $CSVFileName -notype 
     If($displayMessages) 
    {Write-Host "Export file $CSVFileName successfully generated."} 
  
    # Export File To DBServer 
    Copy-Item $CSVFileName $FolderToPutCSVName 
    
    # Email notification 
    If($SMTPServer) 
    {SendEmail $CSVFileName} 
    
    # End 
    If($displayMessages) 
    { 
        Write-Host -ForegroundColor Green "---------------------------------" 
        Write-Host -ForegroundColor Green "- Script started : $DateStarted -" 
        Write-Host -ForegroundColor Green "- Script finished : $(Get-date) -" 
        Write-Host -ForegroundColor Green "---------------------------------" 
    } 
    Exit 
}
In my case, i scheduled that script to prepare the migration from SP OnPrem to SPO and implement for example the correct indexed columns.
Fabrice Romelard [MVP]
Original Message (in french):