Get SharePoint Webpart Usage Report

 

During an extranet migration to office 365 we need to find out a custom content editor webpart usage.  That web part was used in many location that I would be waste of time to find all the references. I decided to write a query again extranet site content database to find out webpart usage.

The first thing we need is the “Webpart type ID”. Download this tool from http://easywebparttypeidgen.codeplex.com/releases/view/48886  and follow the instruction http://easywebparttypeidgen.codeplex.com/  to retrieve webpart type ID.

Now open SQL management studio and run the below query against SharePoint site content database.

Note:  Make sure that you have replaced the ‘Webpart type ID here’ with the right 32 digit webpart type id in the below query before you run it.

SELECT DISTINCT D.SiteID, D.WebId, W.FullURL as WebURL, D.Id As DocumentId,
D.DirName, D.LeafName, tp_ID As WebPartSK
FROM       dbo.Docs D WITH (nolock) 
INNER JOIN dbo.Webs W WITH (nolock) ON D.WebID = W.Id
INNER JOIN dbo.WebParts WP WITH (nolock) ON D.Id = WP.tp_PageUrlID
WHERE WP.tp_ListId Is Null AND WP.tp_Type Is Null AND WP.tp_Flags Is Null
AND WP.tp_BaseViewID Is Null AND WP.tp_DisplayName Is Null 
AND WP.tp_Version Is Null
AND WP.tp_WebPartTypeId='Webpart type ID here'

Run the query and you will see the result table with all webpart references.

If you have multiple content databases then you can run this query against each of them to find all webpart references.