Friday, November 02, 2007

Where is my content type used

If you have worked extensively with MOSS content types you have been in situations where you couldn't locate where the content type was used. Thanks for this blog you can now use the SQL below to retrieve that information.

DECLARE @ContentTypeName nvarchar(128)
SET @ContentTypeName='Document'
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


Kwoque said...

You can also use the Content Query WebPart to search through the whole site collection, just select the Content Type you want and search through the whole site from the root.

Kwoque said...

You can also use the default Content Query WebPart and let it search from the root through all subsite and you can delete every file which is in the Site Collection. But don't forget to unlink the Library/List of the content type :)