There may be a number of reasons why you or your organization may want to understand the impact a particular web part may have on the SharePoint farm. The information will allow you to understand the scope of sites that may be affected from a web part perspective.This information may be helpful in the following scenarios.
·Upgrading from SharePoint 2007 to 2010.
·Need to remove all instances of web parts
·Communicating change of web parts.
·Modification of custom web parts.
·Evaluate usage of web parts.
There are scripts (PowerShell /API) out on the web that may work, but these scripts have a few short comings. Due to the way PowerShell accesses SharePoint objects, the script may run into memory issues when run on large site collections. The other caveat is that the PowerShell/API script may have an issue identifying web parts that do not exist in the GAC (global assembly cache). In this case the object will return a assembly value of “Microsoft.SharePoint.WebPartPages.ErrorWebPart” and the “Title” property will be blank, making it impossible to identify the web part. Any PowerShell or application that uses the API will require traversing the entire scope (Farm, Site Collection etc…) to produce results. As a result the inventory process can take hours and/or days.
This post will provide you with a safe/simple way to inventory web parts in your SharePoint farm in a matter of minutes.The process requires that you have read access to the content databases via “SQL Server Management Studio”.
Note: Running SQL queries directly against the SharePoint content database is not supported. It essentially voids the Microsoft support contract for the entire farm. Here is a link from MS Support about direct access (http://support.microsoft.com/kb/841057). It is recommended that this process be run in a test environment and never in a production environment.
The first step is to create a blank page/site in SharePoint. The purpose of this page is to have a blank site to reference in the in the content database. For my example I create a site named “JagunTestSite” and I will use the pages “default.aspx”.
Log into the SQL server and run the following query.
select d.DirName + ‘/’ + d.LeafName as [URL]
,w.tp_ID as [GUID for WebPart]
,w.tp_ZoneID as [WebPart Zone]
,tp_WebPartTypeID as [Web Part Type ID]
,tp_DisplayName
,tp_ContentTypeId
from <ContentDatabaseName>.dbo.AllDocs d WITH(NOLOCK)
join <ContentDatabaseName>.dbo.WebParts w WITH(NOLOCK)
on w.tp_PageUrlID = d.Id and w.tp_SiteId = d.SiteId
where d.IsCurrentVersion = 1
and DirName like ‘%JagunTestSite%’
and LeafName like ‘%default.aspx’
This query should initially produce zero results. At this point you need to edit the page and add one of the web parts that you are interested in. Re-run the query an you should have one record.
The image above highlights two columns. Column tp_ID (GUID for WebPart) identifies the instance id for the web part added to the page. Column tp_WebPartTypeID (Web Part Type ID) identifies the web part by type (as it exists in the gallery).Record/Copythe “WebPartTypeID” an use the value in the query below.
select d.DirName + ‘/’ + d.LeafName as [URL]
,w.tp_ID as [GUID for WebPart]
,w.tp_ZoneID as [WebPart Zone]
,tp_WebPartTypeID as [Web Part Type ID]
,tp_DisplayName
,tp_ContentTypeId
from <ContentDatabaseName>.dbo.AllDocs d WITH(NOLOCK)
join <ContentDatabaseName>.dbo.WebParts w WITH(NOLOCK)
on w.tp_PageUrlID = d.Id and w.tp_SiteId = d.SiteId
where d.IsCurrentVersion = 1
— Resulting “Web Part Type ID” from query above
and tp_WebPartTypeId = ‘9AFE11F2-9603-AC36-62A9-DEBEB61BCAC0’
This query should provide you with a list of all instances of the web part along with the location. The total number of instances of the web parts can be identifiedby the number of rows returned (see the above image).You can then modify the query to include all content databases (see example below).
select d.DirName + ‘/’ + d.LeafName as [URL]
,w.tp_ID as [GUID for WebPart]
,w.tp_ZoneID as [WebPart Zone]
,tp_WebPartTypeID as [Web Part Type ID]
,tp_DisplayName
,tp_ContentTypeId
from<ContentDatabaseName>.dbo.AllDocs d WITH(NOLOCK)
join <ContentDatabaseName>.dbo.WebParts w WITH(NOLOCK)
on w.tp_PageUrlID = d.Id and w.tp_SiteId = d.SiteId
where d.IsCurrentVersion = 1
and tp_WebPartTypeId = ‘9AFE11F2-9603-AC36-62A9-DEBEB61BCAC0‘
union all
select d.DirName + ‘/’ + d.LeafName as [URL]
,w.tp_ID as [GUID for WebPart]
,w.tp_ZoneID as [WebPart Zone]
,tp_WebPartTypeID as [Web Part Type ID]
,tp_DisplayName
,tp_ContentTypeId
from<ContentDatabase2>.dbo.AllDocs d WITH(NOLOCK)
join<ContentDatabase2>.dbo.WebParts w WITH(NOLOCK)
on w.tp_PageUrlID = d.Id and w.tp_SiteId = d.SiteId
where d.IsCurrentVersion = 1
and tp_WebPartTypeId = ‘9AFE11F2-9603-AC36-62A9-DEBEB61BCAC0‘
Remember this process can be used for SharePoint 2007 and SharePoint 2010.
