I’ve found that working with InfoPath is becoming more and more of a chore. A form I’ve been working on has had its promoted columns changed a number of times (Side note: I would just love to have a defined specification).
Today when I attempted to completely retract my form (inlcuding manually deleting the content type associated with the form), I was presented with the always helpful error “The content type is in use“. This is by far one of the more helpful errors you will ever see from SharePoint. Unfortunately it isn’t clear how to discover where the content type is actually in use. A quick trip to the content database for the site collection you are working in can show us:
DECLARE @ContentTypeName nvarchar(128) SET @ContentTypeName='Content Type Name Here' SELECT w.Title AS [Web Site], w.FullUrl AS [Web Url], al.tp_Title AS [List Title], ct2.* FROM ContentTypes ct1 JOIN ContentTypes ct2 ON LEFT(ct2.ContentTypeId, Len(ct1.ContentTypeId))=ct1.ContentTypeId LEFT OUTER JOIN dbo.ContentTypeUsage ctu ON LEFT(ctu.ContentTypeId, Len(ct2.ContentTypeId)) = ct2.ContentTypeId LEFT OUTER JOIN dbo.AllLists al ON ctu.ListId = al.tp_Id AND ctu.WebId=al.tp_WebId LEFT OUTER JOIN dbo.Webs w ON al.tp_WebId = w.Id WHERE ct1.ResourceDir=@ContentTypeName
[Credit to Curtis Ruppe]
Once we know where the content type is in use, deleting it through the GUI is a trivial matter.
On the occasion were this doesn’t work use:
select * from Alllists where tp_ContentTypes like ‘%CONTENTTYPEIDHERE%’
Hopefully you’ll end up with one result, if so then change to delete from All….
And it’s done! You should now be able to delete the bloody content type!
Mr. Anderson
Thats the worst advice I’ve seen because it would result in list being delete. MS also advices not touching the database or it will render to your agreement being void.
But when you cannot see the item in the GUI, or there is no item in the GUI, in the list that the database says contains it, then at least Mr Anderson’s solution is available — or, of course you can cry “no, no, it’s not supported”, and sit in a corner, and beat your head against a wall for the rest of your life — but then you never have solved the problem.