I know that Microsoft says don’t query the databases directly, and that’s still a good practice for any application level code.  But just like in http://iedaddy.com/2012/03/sharepoint-2010sql-view-to-get-user-profile-property-bag-values/ sometimes it’s a quick and dirty way of getting things done, especially when dealing with content database cleanup or when you only need to run the results once or twice to get the information you need.

I had an issue recently where a webpart was being deprecated, and as such we needed to remove it from several large site collections.  Problem was that as this was a Site Collection in the wild, with active users and admins whom have inserted this webpart all over the place, getting rid of the references seemed like it was going to be a slow and painful process.  Removing it from the WebApp and letting things break was not an option.  Now we could use PowerShell to run through all the webs and subwebs, gathering each page and loading up the WebParts collection to see if it’s in there, but in a live farm that gets expensive.

Instead, we can run a very simple SQL query against the content database looking for a specific WebPartTypeID, and then use the list generated to browse to each page and correctly either remove or substitute in a new webpart through the SharePoint UI (which would be Microsoft recommended).

Here is the T-SQL code:

SELECT DISTINCT 
CAST( D.DirName AS varchar) + '/' + CAST(D.LeafName as varchar) as PageURL, 
tp_ID As WebPartSK, 
WP.tp_WebPartTypeId
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_WebPartTypeId='<your web parts id>'

This code will give you a nice list of all the URLs of pages that contain a particular type of WebPartTypeID.  If you don’t know what a particular WebPartTypeID is, the easiest way to find out is to create a page and place the target WebPart on the page, then run the query filtering for just the PageURL of the page you just created.