I recently had the need to locate all list columns in a SharePoint site of a particular data type. My first thought was someone must have done this before and after a quick hunt around in Google discovered that it wasn’t the case.

I wrote a simple script to output the names of lists and fields of a particular type using PowerShell. It is easy to modify the script to look for other field types or to change the field configuration e.g. switching from plain text to rich text.
This MSDN Article contains details of the “SPField Class” and is very handy if you are looking to create a variation of my script.
http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spfield.aspx

A small word of warning…always test this in a test environment before running in production. Use this script at your own risk!

Here is my base script:

$SPwebApp = Get-SPWebApplication "http://myintranetURL"
 foreach ($SPsite in $SPwebApp.Sites)
 {
  foreach($SPweb in $SPsite.AllWebs)
  {
   write-host "Site URL: ", $spweb.url
   foreach($list in $spweb.lists)
    {
     write-host "List Name: ", $list.title
      foreach($field in $list.fields)
       {
        if ($field.type -eq "Note" -and $field.title -ne "Approver Comments")
         {
           write-host "field title: ", $field.title, " > ", $field.type -nonewline
           if ($field.richtextmode -eq "FullHTML")
            {
              write-host "* Rich Text Enhanced ** " -nonewline
            }
          write-host ""
        }
     }
    }
  }
 }

This scripts walks its way through every list in a given web application and outputs a list of “sites, lists and field names where the field type = “Note” (multi-line text).

Works on both SharePoint 2010 and SharePoint 2013.