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

Project Server - People picker

When user customizes Project Detail Pages in Project Server, he can add many Custom Fields to one page. He can choose from six types of Custom Fields: Cost, Date, Duration, Flag, Number, Text. That is good, but it isn't enough. Where is "People picker" field type?


If you open Project Detail Page called Project Information, you can see default field for Owner. It is a people picker field:




Well, if this field type exists on default Project Server Pages, how come it can not be added to customized page?

I actually don't know the answer to that question, but it is a fact that you can not add this field type to your page.

But, there is another solution. People picker for Project Server exists in another form. In some of my previous posts, I mentioned Solution Starters package from Microsoft (when I talked about Dynamic Workflow).

In this package, you can find solution for people picker field, it is called PDPCustomization.

If you want to use people picker field in your forms, first you need to download Solution Starters package from this link:
http://archive.msdn.microsoft.com/P2010SolutionStarter

EDIT (8.6.2014.)
Microsoft has retired link for downloading this solution, so for downloading Solution Starters go to these links depending on the version of Project Server you are using:
Project Server 2010:
https://drive.google.com/file/d/0Bz62Vp3ZwGCrRDBCdjNrYUNYWmM/edit?usp=sharing
Project Server 2013:
http://www.fluentpro.com/solutionstarters2013.html 

Look for PDPCustomization.zip file in that package, extract it and deploy solution to your Project Server.

After the solution is deployed, to add a people picker to the page, web part of deployed solution must be added to the page. Web Part is called AutoComplete Web Part and it is located in PDP Customization folder:







Position this new AutoComplete Web Part under the existing Web Part on the page.
Click on Edit Web Part on the Web Part menu. Menu with properties of that Web Part will open. In that menu, you can choose the group from which users will be fetched and field name in which the name of this user will be saved (this field is Custom Field of type "Text").



After the changes have been saved, your custom field will now read users from Administrators group.