Monday, February 25, 2008

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:

  1. Site Actions->Site Settings.
  2. Under Galleries click Site content types.
  3. Find the content type you wish to delete and click on it.
  4. 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

  1. Run the SharePoint Explorer (SPE) on the server that's hosting the WSS site.
  2. 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. Sharepoint Content Database Name and SQL Instance Name
  3. Click on the Site Collection itself and grab the ID property of the site, we'll need this when we run the stored procedure. Identifying the Site Colleciton Id with Sharepoint Explorer
  4. 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.
  5. 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. Result set from proc_ListContentTypeInUse, finding all the lists in the site collection that use a given Content Type 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

27 comments:

Anonymous said...

Great post - but how do I know *which* list this content type belongs in?

My result is "Pages" and I have several page libraries in my Site Collection (it's a Publishing/WCM site with multiple sub-sites).

Anonymous said...

Ok, you can get the second query from the stored procedure that reuturns the list's name:

exec sp_helptext 'proc_ListContentTypeInUse'

Then, paste this into a new query and add "*" to return all fields from the "ContentTypeUsage" table.

Then using the returned WebId and ListId, you can find the list:

select * from webs where id = GUID'
select * from alllists where tp_id = 'GUID'

Remember that you need to "Allow management of content types" in the List's Advanced Settings then you get delete the List Content Type and finally you can delete the Site Content Type.

Kevin Jones said...

Great post. You saved me some time. Thanx for the info

Anonymous said...

Great post man. Thanks a bunch.

Anonymous said...

Can anyone tell me how to run stored procedure with MS SQL Server Management Studio? All i see there is scripts and more scripts and if i just copy needed values i get errors... Please just explain how to corectly do this.

Tyler Holmes said...

You bet.

To run a query in the Management Studio first connect to it. You can find out how to do that here.

After you've connected you'll see a list of databases on the left hand pane under the name of the server instance that you just connected to.

Right click on the database you want to run a query against and choose 'New Query'. A new query window will come up where you can run some SQL.

At this point you should be able to copy/paste some of the SQL you've seen in this post (and some of the comments) and run it by pressing F5 or clicking the Play button in the tool bar.

If you get an error it'll probably be either cause the SQL syntax is unsound OR the object (like the stored procedure listed above) is not in the database you're currently running queries against. I'd make sure you're running queries against a SharePoint database as opposed to some other SQL Server database (like 'Master'). You can see (and cahnge) which database your queries are running against by looking at the drop down box at the top of the Query Pane.

I hope that helps.

Best,
Tyler

Anonymous said...

Thanks, now it works without errors, but the strange thing is - it doesn't return any data either.
I've tried the built in SharePoint script:
USE [WSS_Content]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[proc_ListContentTypeInUse]
@SiteId = fab41e81df894799bc2c9c4bcbe6496a0,
@ContentTypeId = 0x010104

SELECT 'Return Value' = @return_value

GO

that gives me nice errors:
Msg 8114, Level 16, State 1, Procedure proc_ListContentTypeInUse, Line 0
Error converting data type nvarchar to uniqueidentifier.

So i'm pretty much stuck in this .

Tyler Holmes said...

If you're calling it like the blog post suggests ([proc_ListContentTypeInUse]
@SiteId = fab41e81df894799bc2c9c4bcbe6496a0,
@ContentTypeId = 0x010104) and you're getting no results back it means that the given content type (whatever 0x010104 represents) is not being used in the given sub site (whatever that GUID represents).

Hope that helps,
Tyler

Anonymous said...

Awesome..just what the doctor ordered!!!

Shekher said...

Probably too late, but site id is string,so put it in single quote

Darryl said...

Thank you, saved me hours!

Sameer Dhoot said...

This is awesome!! saved me a lot of time... for some reason i could not use the SP as it gave me conversion error from varchar to varbinary i tried to used the convert function but did not work.

Anyway the other SQL query did the job..

Anonymous said...

Just to let you know there an object In the SharePoint Object Model called "SPContentTypeUsage" who is doing all the job without going to the database. You should give it a glance before going to the DB ;-)

elSuizo said...

marvellous. Thank you, it saved me some time and awful fiddling around, indeed.

Football Motormouth said...

There is one more thing you can try in Sharepoint 2010. Once you delete all lists or other items that use the content type, check the Recycle Bin for the site and make sure you empty it out. After that, take a look at the Site Collection recycle bin and make sure you empty that out as well.

Lars Nielsen said...

The easiest way of all is to get hold of the SharePoint Manager tool on Codeplex. Run SharePoint manager and you can navigate to the site, look at the content type in question and then look at the Usages branch which will tell you the URL of the lists or libraries that use that content type.

kollega said...

Very useful post! Thanks!

Big T said...

If you want to see the name of the list as well as the ID use this.

SELECT
ctu.SiteId
, sys.fn_varbintohexstr(ctu.ContentTypeId) AS ID
, ctu.WebId
, ctu.ListId
, ctu.IsFieldId
, ctu.Class
, al.tp_Title
FROM ContentTypeUsage ctu
JOIN AllLists al on al.tp_ID = ctu.ListId
WHERE (sys.fn_varbintohexstr(ContentTypeId) LIKE '[CONTENT TYPE ID]%')

Yatish said...

In my case the stored procedure to check the content type usage returned nothing, but if you go and check the ContentTypeUsage table, it still had an entry for that content type.
Sharepoint manager also shows the usage as 0, but still it can't be deleted

Clem said...

Is there any means to delete content types in SP 2010? The table in this query doesn't exist and SharePoint Explorer does not exist for 2010.

Josh Gruberman said...

I have a site that has a large number of subsites and libraries under it. I am trying to remove a content type from the site collection, but there are 6740 libraries(yes, really) that have the content type assigned to them.

Is there a way to remove the content type on a mass scale from the libraries via Powershell for WSS/2007?

Tyler Holmes said...

Hey Josh,

I'm not sure what's positive in SP 2007 with power shell (although I know its available). It IS possible via the .NET API.

As a last resort, consider opening Visual Studio.

Best,
Tyler

Anonymous said...

Great Post. Really helped in solving the issue for me. Have referred your article in my blogs so that can share it with more people and help the community.
http://community.zevenseas.com/Blogs/Geetanjali/Lists/Posts/Post.aspx?ID=4

Keep up the good work. :)

Expert SharePoint Developers Blog said...

There is noticeably a bundle to know about this. I assume you made certain nice points in features also.

westerdaled said...

SELECT SiteId,
Hi

with this I get 8 entries for the same site and web but different lists. Now this is confusing, as the Page layout content type should only be in the /pages of the home site. I need to track down this duplicate and redundant content type.

sys.fn_varbintohexstr(ContentTypeId) AS ID, WebId, ListId, IsFieldId, Class
FROM ContentTypeUsage
WHERE (sys.fn_varbintohexstr(ContentTypeId) LIKE '0x010100C568DB52D9D0A14D9B2FDCC96666E9F2007948130EC3DB064584E219954237AF3900242457EFB8B24247815D688C526CD44D007F51F315FA8DE740AF083B5BC8141DFF%')

westerdaled said...
This comment has been removed by the author.
rayyan said...

it was Informative Post,and Knowledgable also.

Rayyan