Finding the name of SP list column in SP database

In my previous post, I've demonstrated how does SharePoint store items of its lists into SQL database.

But, as mentioned in that post, columns in SharePoint's SQL database (WSS_Content), in AllUserData table where all data is stored, have names like nvarchar1, navarchar2, nvarchar3...

There are two ways how can you find the name of column in which data from your SP list is stored:


1. Powershell script:

Change values marked in red with your values:

// Get your SPWeb

$web = get-spweb "http://yourserver/yoursite";

// Get your SPList

$list = $web.Lists["ListName"];

// Get the field you want to examine

$field = $list.fields["FieldName"];

// Parse the schemaxml into a PowerShell xml object

[xml]$schema = $field.SchemaXml;

// You can now iterate thought the xml attributes. For example the colName:

$schema.Field.ColName; 

2. C# function:

This solution is much better because it tells you the name of the column in which data is stored and it also tells you the row in which this data is located.

//list is the name of your SP list, field is the name of SP field

public void getXmlColName(string list, string field)

{

       string _siteUrl = ConfigurationSettings.AppSettings["YourSiteURL"];

       using (SPSite _siteCollection = new SPSite(_siteUrl))

      {

using (SPWeb _SPWeb = _siteCollection.OpenWeb())

             {

                   

                    SPList _list = null;

                    SPFieldCollection _listFields = null;

                   

_list = _SPWeb.Lists[list];

                    _listFields = _list.Fields;

                   

                    string _xmlSchema = _listFields.SchemaXml;

                    //searching for name of the field in entire xml scheme

                    int _startIndex = _xmlSchema.IndexOf(field);

                    if (_startIndex != -1)

                    {

                        int _endIndex = _xmlSchema.IndexOf("/>", _startIndex);

                        string _stringSearch = _xmlSchema.Substring(_startIndex, _endIndex - _startIndex);

                        int _startIndexColName = _stringSearch.IndexOf("ColName=\"") + 9;

                        int _endIndexColName = _stringSearch.IndexOf("\"", _startIndexColName);

                        //this is ColName

                        string _xmlColName = _stringSearch.Substring(_startIndexColName, _endIndexColName - startIndexColName);

                        Console.WriteLine("\n\nName of column in which your data is stored (ColName): " + xmlColName);

                        //now we need RowOrdinal, so that we can see in which row is data located

                        int _startIndexRowOrdinal = _stringSearch.IndexOf("RowOrdinal=\"") + 12;

                        int _endIndexRowOrdinal = _stringSearch.IndexOf("\"", _startIndexRowOrdinal);

                        string _xmlRowOrdinal = _stringSearch.Substring(_startIndexRowOrdinal, _endIndexRowOrdinal - _startIndexRowOrdinal);

                        if (_xmlRowOrdinal.Length > 1)

                            Console.WriteLine("\n\nRow index (RowOrdinal): No index");

                        else

                            Console.WriteLine("\n\nRow index (RowOrdinal): " + _xmlRowOrdinal);

                    }

                    else

                        Console.WriteLine("There is no SP field with that name");

                }

            }

        }

Now, when you have  column name and RowOrdinal, you can go to SQL database and search data with following query (let's say that your data is in nvarchar1 and RowOrdinal is 0):

/****** Script for SelectTopNRows command from SSMS  ******/

SELECT nvarchar1

FROM [WSS_Content].[dbo].[AllUserData]

WHERE tp_RowOrdinal = 0

 
 

Where does SharePoint store list items?

In one of my previous posts, I've described where does SharePoint store all documents (like pdf, doc or xls files) from its document libraries.

But, items from other lists are not stored at the same place. They are stored in the same SQL database, but in a different table and in a very odd fashion.

NOTE: This post refers only to SharePoint 2010. 

NOTE 2: It needs to be noted that Microsoft does not recommend that you execute any SQL queries directly on SharePoint databases, or to be specific, it is not allowed.

But, sometimes, it is the easiest and quickest way of getting data you need.  

List of all SharePoint lists is located in dbo.AllLists table in WSS_Content database. Next image shows query in SQL Management Studio: 



Name of your list can be found in tp_Title column. If you want to see all the data of that list, then you need to copy its ID from tp_ID column and go to dbo.AllUserData table. ID of that list can also be found in the url when you open list settings in SharePoint:

Server_Name/Site_Name/_layouts/ListEdit.aspx?List={4B656BB9-0193-49D9-B5E4-0D76B6601198}

Now, if you go to dbo.AllUserData table with following query, you can see data of you SharePoint list:




Now, the columns which you get with this query have name like: nvarchar1, nvarchar2…int1, int2…datetime1…float1. 

If you want to know in which column is your data located, there are many ways, but I will explain three of them.

First, and very stupid way is comparing data from SharePoint list with data in AllUserData table looking for the same name.

For example, name of SharePoint list is usually stored in nvarchar1 column, so, if you need to find your names of all your SharePoint lists, you can look for it with following SQL query:

SELECT     nvarchar1 AS Title

FROM       [WSS_Content].[dbo].[UserData]

WHERE     tp_ListId = '4B656BB9-0193-49D9-B5E4-0D76B6601198'

In AllUserData table, number of columns of certain type is fixed (for example: there are 8 columns of datatime type), but you can have more then 8 datatime columns in SharePoint list. If there are more then 8 columns of datatime type in SharePoint list (all 8 datatime columns ar filled), in SQL table AllUserData, this ninth data will go in the datatime1 column, but in the next row. So, now this list item will occupy two rows in SQL table, and this will be marked in tp_RowOrdinal column. Every new row will have the same tp_ID like others, but tp_RowOrdinal will be incremented by one with each new row, and each new row get 8 new datatime columns. Same procedure works with other types of data, but there does not have to be 8 columns of certain type, for example, there are 64 of nvarchar type columns.

In my next post, I will show you how can you see in which column is you data stored using Powershell and C#.