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#.