I found this peice of SQL on the web a while ago, I have no idea where it came from but it did come in handy the other day when a client needed to delete a content type but kept on getting the message it was in use.

When deleting content types you need to ensure:

• All items using the type are deleted (and deleted from the recycle bin)
• The Content type is removed from any list using it.
• Any Page Layouts using the type nolonger reference it.

To help track down where the content type is in use the following query will help.

DECLARE @ContentTypeName nvarchar(128)
SET @ContentTypeName='ABCC.CourtCasePage'
SELECT w.Title AS [Web Site], w.FullUrl AS [Web Url], al.tp_Title AS [List Title], ct2.*
FROM ContentTypes ct1
JOIN ContentTypes ct2
      ON LEFT(ct2.ContentTypeId, Len(ct1.ContentTypeId))=ct1.ContentTypeId
LEFT OUTER JOIN dbo.ContentTypeUsage ctu
      ON LEFT(ctu.ContentTypeId, Len(ct2.ContentTypeId)) = ct2.ContentTypeId
LEFT OUTER JOIN dbo.AllLists al
      ON ctu.ListId = al.tp_Id AND ctu.WebId=al.tp_WebId
LEFT OUTER JOIN dbo.Webs w
      ON al.tp_WebId = w.Id
WHERE ct1.ResourceDir=@ContentTypeName