Deleting unused SharePoint Content Types

It happens to every site collection admin at some stage. For whatever reason you're required to replace a defunct content type in your document library with a new one. Adding t he new one is easy. But then you try to delete the old one and receive the terrifically informative "Content Type is still in use" error.

You've tried everything:

• Updated any files you could find with the new content type.
• Checked every file twice to make sure you didn't miss any.
• Managed Checked Out files to locate all those tricky 'hidden' docs that haven't been checked in yet and only exist in some funky temp/draft state! (You'll either need to take ownership of these as site collection admin, or email a list to the original authors to get them to check them in or delete then. I can't believe there is no option to delete these en masse!)
• Run custom CAML queries to make REALLY sure you didn't miss any (using the fabulous U2U SahrePoint CAML Query feature - www.u2u.be/res/Software.aspx.
• Written a console app just to make ABSOLUTELY sure you didn't miss any.
• Emptied the recycling bin - both of them!

But still the error persists.

Just when I thought I'd exhausted all options it occurred to me that perhaps versions were the culprit. Looking at the version history for a few suspect documents confirmed that they were.

Running the following SQL query will find them all.

SELECT *
FROM AllUserData
WHERE (tp_DirName LIKE '%sitename/Shared Documents%')
AND ((tp_ContentType = 'myDodgyCT'))
ORDER BY tp_DirName

This should be self-explanatory but tp_dirName is just using a relative path from the domain to the library. And ContentType is the explicit name of the content type to search on.

From there I just exported the results to Excel, filtered out duplicates and was left with a workable list of documents. Simply publish a final version of each (if required), then go to the Version History and delete all previous versions.

You could do all this in one step by turning off versioning for the library and then turning it on again. But this would delete versions for ALL documents. A good trick to remember when you're site quota is reached.

Good luck!