Change an item content type with a SQL Query

December 8, 2008


I’m moving to a new site ! Please visit this article to by clicking the following url: Change an item content type with a SQL Query

Some months ago, I was involved in a document library migration from SP2001 to SP2007 project. One of the big problems I found was how to upload all the files to Sharepoint with the standard web services interface and set the content type of the items. You can read more about that issue and the solution implemented here

The problem with that approach is that it won´t work well when thousands of files are migrated. At the final migration process to production that took place this week, we moved around 70k files to the new server. Although the files were successfully uploaded, the update content type method didn´t work for many of them.

As we were in the middle of the migration process, I tried to find a different solution for bulk updating all the files and setting the correct content types. At first, I tried writing a console application that would update all the files via Sharepoint Object Model. The problem with this approach is that the process would take a lot of time to process all the 70k files.

As we had not enough time to run that process, I finally decided to try a don´t-do-this-at-home strategy. I wanted to update directly the Sharepoint database to set all the appropiate content types.

At the content database of the site collection where I was migrating all the documents, I opened the AllUserData table where all list items are stored. That table contains the fields tp_ContentType and tp_ContentTypeId that seemed like what I was looking for. Additionally, there is a ContentTypes table where all the Content Types for the SiteCollection are stored. I already knew the name of the content type I wanted to set so I only needed the Id. I tried to build a simple update query:

UPDATE    
       AllUserData
SET              
       tp_ContentType = '<content type name>', 
       tp_ContentTypeId =
                          (SELECT ContentTypeId
                            FROM   ContentTypes
                            WHERE  
                                       ResourceDir = '<content type name>')

WHERE     (tp_DirName = '<document library name>/<folder>') 

Some comments on the previous sql:

I only had one web and one document library for this migration process. In a different scenario, you would have to filter by tp_WebIdand tp_ListIdto change only the items of a specific location

I found the field ResourceDir in the Content Types table that had the name of the content type. You could also use directly the content type Id, converting it to varbinary which is the type of the tp_ContentTypeId field. However, there is a problem with that, as I’ll point out later.

Use tp_DirName to filter for a specific folder in the document library. You could also use it to filter by document library name but I strongly suggest you to use the tp_ListId field instead.

So far so good, everything looks pretty straightforward. The problem with this code, is that it won’t work as expected. The tp_ContentType field will be properly set but it won´t recognise the item with the new Content Type.

After testing the query, I decided to manually set the content type of an item with the standard Sharepoint interface and see what changed at the data level. What I saw was that the tp_ContentTypeId field was set to the id of the new content type but it also appended a Guid value at the end i.e: My content type id was “0x01017013” but the value at the AllUserData table was “0x01017013”. I didn’t know where did that guid came from nor had enough time to research it (if anybody knows it please leave a comment !!!) so I tried a different approach. What I did is, after I manually changed one of the items content type with the Sharepoint interface, I run the following Sql Query:

UPDATE    
     AllUserData
SET              
     tp_ContentType = '<content type name>', 
     tp_ContentTypeId =
                          (SELECT TOP 1 tp_ContentTypeId
                            FROM          AllUserData
                            WHERE      tp_ContentType = '<content type name>' and 
                            tp_DirName = '<document library name>/<folder>')
WHERE     (tp_DirName = '<document library name>/<folder>') 

Basically what this sql does is update all the items of the list folder and assigning the Content Type Id from the previously manually changed item (with the value assigned by the Sharepoint Object Model, including the appropiate guid value at the end)

After running that query, all the items of the list folder had the correct updated content type and in less than 1 second !

That’s all, as always, comments will be appreciated 🙂