I have come across this problem a few times now. You want to delete the site column and get an error like… You can’t delete this column because it is still in use. Or Fields associated to content types cannot be deleted. The main problem is that how do I know what site or list is using the column? Searching around the web I found a few suggestions however only one had a suggestion other than delete everything and start over or create everything new and leave the old as it is. However that one was using SQL queries directly against the SharePoint DBs and although it was only reads which is better then making writes directly it is still against Microsoft Support rules so I had to find another way.
PowerShell to the rescue! After doing a little digging it became apparently clear that it is actually very easy to find all instances of a site column within a site collection. The short little script that I will share today will look at every site and every list within a site collection and return the GUID for the site and list that it finds that column. This is great because now you can easily identify where the column is and go to those lists and remove it so that you can safely delete the site column. Better yet, we can pipe the results to a PowerShell cmdlet to remove the column all at once!
So here is what I used
First we get the site collection that contains the site column
$web = Get-SPWeb http://”sitecollectionurl”
then we get the column that we are looking for
$column = $web.Fields[“Column Display Name”]
Then we find where it is used
This returns a list of WebID and ListID
its both GUIDs which kind of stinks since we don’t memorize those. So next what you can do to get the URL is put in Get-SPSite http://sitecollectionurl | Get-SPWeb | select URL, id