"This content type is already in use" error while deleting content type

If you have been working on SharePoint and playing with content types and lists (which I am very sure you all must have) then "This content type is already in use" is a nightmare that you can think of when trying to delete a content type.

As the name suggests, this error indicates that the content type is in use somewhere and you cannot delete it. Lets say that we have created a list and we have attached a content type to it. Now if we want to delete the content type , then 

1. normally we first delete the content type association with the list 

2. and then we delete the list 

3. and finally delete the content type.

If you miss the first step and delete the list directly, you get stuck with an orphaned association.

This will generate the above error when you try to delete the content type.

 Every time I get this error, I used to avoid the situation and went for an easy approach to deploy it to a new web application. That's an easy job. But I had sometime today to look deeper into the issue and try to fix it for once and for all.

So what could actually be the issue. Well, we know that the content gets stored in content database in sharepoint so all this information like list, content types and their association must be getting stored in database. That means that when we delete the list then its entry is getting deleted from the database but somewhere the association between content type and list remains that prevents us from deleting the content type.

But where is that information stored? In search for an answer I came across this wonderful blog by Tyler Homes :

http://blog.tylerholmes.com/2008/02/deleting-content-types-in-sharepoint.html


He has mentioned in his blog about the table in the wss content database where this association is stored.

ContentTypeUsage : is our table of interest that holds all the associations between the list and the content type. Query this table for the problematic content type. Delete all the associations and you are good to go.


Below query will help you fetch the association between the lists and the content type.

select * from ContentTypeUsage WHERE (sys.fn_varbintohexstr(ContentTypeId) LIKE 'Content Type ID%')​

Once you get the associations, just remove them and you are good to go. Now if you will try deleting the content type, you will be able to do it in one go.​

Thanks to Tyler for his cool blog.