This is (a backup copy of) the article by Tyler Holmes which is mentioned by Marius Constantinescu. I have used it a couple of times and I would hate to see it disapear...
DELETING CONTENT TYPES IN SHAREPOINT 2007: A TROUBLESHOOT
[Updated Nov, 13 2008]
Whenever it comes time to delete a content type, it's either a 10 second job or a multi hour why-did-I-become-a-developer endeavor. Either the content type is completely orphaned (it's not being used at all) and easy to delete, or it's used in a tonne of places, most of which will be difficult to find and the treasure hunt is on! It's of note that before a content type can be delete there can be no items in any lists that use it and it can not exist in any lists.
This blog post will cover how to delete a content type and how to troubleshoot the most common error, The content type is in use.
Deleting a Content Type
For the record, the series of steps to delete a content type are as follows:
- Site Actions->Site Settings.
- Under Galleries click Site content types.
- Find the content type you wish to delete and click on it.
- Click the Delete this site content type link.
At this point the content type will either be deleted, or you'll get an error message saying something like:
The content type is in use.
If you have errors turned on in the web.config (setting customErrors="Off" and CallStack="true") your error message may look a lot more like:
The content type is in use. at Microsoft.SharePoint.SPContentTypeCollection.DeleteFromWeb(SPContentTypeId id, String strName) at Microsoft.SharePoint.SPContentTypeCollection.Delete(SPContentType Id id) at Microsoft.SharePoint.SPContentType.Delete() at Microsoft.SharePoint.ApplicationPages.ManageContentTypePage.DeleteContent Type() at Microsoft.SharePoint.ApplicationPages.ManageContentTypePage.System.Web. UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
If you get one of the messages above then you need to get a little more creative. Your options are either digging through all the lists in the site collection and trying to figure out who's using the content type, OR running a stored procedure to determine the lists that are using the content type. I don't know about you but the latter sounds a little more time efficient.
Finding Lists/Libraries using the Content Type
The first thing we'll need is access to the content database with a SQL Client like SQL Management Studio. There's a tutorial on how to connect here.
We'll also need a tool like the SharePoint Explorer for WSS 3.0 (needs to be run on the WSS server itself) to figure out what the SiteCollection ID is.
Ok, lets get to it.
Determining the Site Collection ID of your Site Collection
- Run the SharePoint Explorer (SPE) on the server that's hosting the WSS site.
- Find your Web Application with SPE and click on the Content Databases tab to figure out what the name of the SQL Server instance hosting the content and what the name of the content database is.

- Click on the Site Collection itself and grab the ID property of the site, we'll need this when we run the stored procedure.

- The last thing we'll need is the ContentTypeID of the content type we want to delete, the easiest way to get this is right out of the URL when you go to delete (or edit) the content type (Site Settings->Site Content Types->Click on your content type). Here's a sample URL.
http://w2k3-tyler-virt/_layouts/ManageContentType.aspx? ctype=0x010700037B79D2DD41C24A8F55D82FC6B71FAC&Source=http%3A%2F% 2Fw2k3%2Dtyler%2Dvirt%2F%5Flayouts%2Fmngctype%2Easpx
In this example the Content Type ID is 0x010700037B79D2DD41C24A8F55D82FC6B71FAC.
- Now we open up the SQL Server Management Studio, connect to the [ServerName]\OfficeServers instance and run the following stored procedure against our sites content database (we figured this out in #2).
[proc_ListContentTypeInUse] [SiteCollectionId], [ContentTypeId]
Or in out case:
[proc_ListContentTypeInUse] 'D2F8C831-4CA7-41C0-8497-82F897B61B2B', 0x010700037B79D2DD41C24A8F55D82FC6B71FAC
What you get back is a result set showing all the lists that the given ContentType is being used in.

If you go and remove the content type from all those lists you should be good to go and that pesky error message will go away.
[Updated Nov, 13 2008]
You can also run the following query which will give you the SiteId, WebId, ListId of where the content type is in use!
SELECT SiteId, sys.fn_varbintohexstr(ContentTypeId) AS ID, WebId, ListId, IsFieldId, Class FROM ContentTypeUsage WHERE (sys.fn_varbintohexstr(ContentTypeId) LIKE '[ContentTypeID]%')
Hope that saves someone somewhere some time.
My Very Best,
Tyler