Cleaning up a “Rogue” Column In SharePoint – Removing Site Columns in SharePoint 2007

27Jul09

Hi folks…I (Blaine) have been working on some SharePoint-based products for the last couple of years and ran into an interesting issue the other day: 

Have you ever had a site column that you just couldn’t seem to “exorcise” out of SharePoint..specifically the content type association? 

Usually when you try to remove any semblance of a column the barriers are either that you are referring to that column within a list or list view or web part or content type or workflow or .Net program (or even a DIP panel).  You must remove all references to the column before deleting it (and this could be extensive).  I’ll assume you are removing the column with proper due diligence and not try to provide a manifest of all dependencies within this tip.  Ah, but what if that didn’t work, what if you just can’t seem to remove the column from the content type in the UI? 

Well the column and its datatype may have been defined programmatically! 

For example, SharePoint does not play well with some datatypes (like Integer) that are available for use in .NET.  Although an Integer column can be created programmatically (and is actually functional in SharePoint) it does not play well with the UI.  When you look at the column in the content type it may be grayed out so it cannot be removed and attempts to remove the column from the Content Type using the UI will fail. 

Fortunately, there is a way to remove a rogue column from the content type with a few SQL tricks.  (OK, I know “don’t hack the SharePoint database”…is what everyone says). 

But I am old database guy so I say:

“Try it first on a test Library and backup that library beforehand”

“Don’t hack a production database unless you know it works”

“Always backup your database before you perform magic tricks”

“Perform your magic tricks after hours”

The “Hack”

Columns related to a Content Types are stored in a single column in XML format.  For each content type used with the Library that contains the rogue column you must remove its reference from the XML (stored in the Definition column of the ContentTypes table in the appropriate SharePoint Content Database.  So before you start the procedure make a list of all content types where the column is used.

Now for each content type:

1.  Access the appropriate Content Database and run a SQL query to obtain the definition (column listing) for the content type.

Select Definition from dbo.ContentTypes where ResourceDir = ‘ContentTypeName’  (replace with the content type in your list)

2. Copy the XML contents of the Definition field and paste to Notepad or favorite editor.  Save it as a backup to a local directory.  “SaveAs” a copy to work on.

3. Remove the section that references the rogue column.  In this definition XML you will see a single “ID=” entry and then a repeating tags for each column related to the content type.  It will begin with “Name=” and end with “<FieldRef ID={guid}”.   So do a find on the “Name=yourcolumnname” to know where to start.   After removal there should be a space between the definitions.   Be careful…this step is critical!

4. Now select and copy the edited text to clipboard.

5. Now run a SQL update to modify the same Definition field with the modified XML:

Update dbo.ContentTypes
Set Definition = paste XML copied to clipboard, enclose in single quotes’
Where ResourceDir = ContentTypeName’ (replace with content type)

 6. Revisit the content type UI and refresh…Is the field gone?  Create a document with the content type.  Remember that you may have to alter DIP panels where the column was used. 

7. Remove (disassociate) any site content type associated with a Document Library or List that was using the rogue column (you can readd it later).  If you created any documents with that content type you must delete them first before removing the content type.  You must also delete the Content Type from the Site Content Types.

 8.  Repeat this for each content type that uses the rogue column.

 9.  Now you might want to actually delete the rogue column after removing its reference after all you don’t want it to be re-used.  But there is another problem: You can’t access the DFBarCode through the UI since it’s an integer and its actions are grayed.  That’s OK, just manually type in a URL (like below) to edit the column and then choose to “Delete”.

http://SharePointServer/_layouts/fldedit.aspx?field=RogueColumnName

(Replace SharePointServer with your URL)

(Replace RogueColumnName with your column name)

Advertisements


One Response to “Cleaning up a “Rogue” Column In SharePoint – Removing Site Columns in SharePoint 2007”

  1. 1 foobar

    Bravo. An excellent write-up. I’d searched for two hours before I got to your post. In order to get more hits, you should make the story more Google-friendly by modifying the title, fleshing it out with more relevant words. I would never have searched for a “rogue” column.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: