SharePoint 2010: The content type is in use

When trying to delete a content type within SharePoint you can receive the following error:

Obviously it is telling you that you can’t delete the content type as it is being used somewhere within your site.  The question is where is the content type being used.

When I have been faced with this I have used a couple of different methods to find out.

SharePoint Manager

SharePoint Manager is a tool that you can download here from CodePlex.  It allows you to explore various objects within your SharePoint farm,  including content types and their usages.

SharePoint Manager is a useful tool, however,  in some instances it will indicate that the content type has no current usages but you will still receive the above error message.2.

SQL

The following SQL can be used in instances where SharePoint Manager indicates that the content type is not being used.

SELECT WebId, AW.FullUrl, ListId, AL.tp_Title
FROM ContentTypeUsage As CTU
     INNER JOIN AllWebs As AW On CTU.WebId = AW.ID
     INNER JOIN AllLists As AL ON AL.tp_ID = CTU.ListId
WHERE (sys.fn_varbintohexstr(ContentTypeId) LIKE '[ContentTypeID]%')

You can replace [ContentTypeID] with the content type id within the URL when click on content type within SharePoint: