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)
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
If you like this blog or find it useful for you, you are welcome to comment on it. You are also welcome to share this blog, so that more people can participate in it. If the images used in the blog infringe your copyright, please contact the author to delete them. Thank you!