SharePoint TIP: [MissingWebPart] WebPart class is referenced [1] times in the database - how to locate the webpart

When performing migrations, whether from 2007 to 2010 or 2010 to 2013, you may see the following warning appear: 1) in response to executing PreUpgradeCheck; 2) in the Missing Server-side Dependencies report; and 3) in other event logs:

[MissingWebPart] WebPart class [Webpart GUID] is referenced [1] times in the database [Content Database Name], but is not installed on the current farm. Please install any feature/solution which contains this web part. One or more web parts are referenced in the database [Content Database Name], but are not installed on the current farm. Please install any feature or solution which contains these web parts.

It is difficult to resolve this migration issue since no page location information is provided.  To find this information, you must search several tables in the content database, including the WebParts, AllDocs and Webs tables.  Using inner joins, you can find this information with a single SQL statement, shown below, executed on the source database.  This statement works in SQL Server 2008:

USE WSS_Content_Database
SELECT tp_WebPartTypeID AS 'WebParts.WebPartTypeID(WebPartClassGUID)', 
       tp_PageUrlID AS 'Webparts.tp_PageUrlID', 
       AllDocs.Id AS 'AllDocs.Id', 
       AllDocs.WebId  AS 'AllDocs.WebId', 
       LeafName  AS 'AllDocs.LeafName', 
       DirName  AS 'AllDocs.DirName', 
       ListId AS 'AllDocs.ListID', 
       Webs.Id AS 'Webs.ID', 
       Webs.Title AS 'Webs.Title', 
       FullUrl AS 'Webs.FullURL'
FROM WebParts 
INNER JOIN AllDocs 
ON tp_PageUrlID = AllDocs.Id
INNER JOIN Webs ON AllDocs.WebId = Webs.Id
WHERE tp_WebPartTypeID = '[Webpart GUID]'

Executing this script returns a table containing the above fields, specifically, the page (leaf) and directory (DirName) names, as well as the site title (Webs.Title) and URL (Webs.FullURL) to help you locate the problematic web part:

Executing this SQL script returned the site, SS, containing the problematic web parts.  This script was useful in that it brought to attention a site that had been overlooked in migration preparation and was no longer being used.  As a result, though problematic web parts were seen in the migration report, it was not known where these were.  Navigating to the site, the problematic web parts were immediately identified:

Then, appending ?Contents=1 to the page URL, I viewed the web part maintenance page:

Using the page features, I was able to remove the problematic web parts.  Re-executing stsadm.exe -o PreUpgradeCheck,

and then viewing the SharePoint Products and technologies Pre-Upgrade Check Report, specifically, the Feature Information section, found no more [Missing] feature items.


References

• Test-SPContentDatabase
• How to Remove Missing WebPart during SharePoint Upgrade
• TechNet: Using Inner Joins
• Preupgradecheck: Stsadm operation (Office SharePoint Server)
• SharePoint 2010 Upgrade: Test-SPContentDatabase – Key to Successful Upgrade

Notes

• Thanks to Claus with BoostSolutions for publishing the article on this.  I've made some minor tweaks to his original script.  Note that an inner join command is written as two words, "INNER JOIN."