Deleting Content Types in SharePoint

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