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