Posted by Alec on Wed, 07 Apr 2010, in Sharepoint
Lately I had fun digging into Sharepoint database and see how the monster was built and what its database schema looking like. Well, it's pretty smart the way it was built but it's also a pain for developers to understand and even to play with it, if they have to. With the business that I am working for, we have to create reports for corporates, which forces us to having to dig into Sharepoint database directly, even though I read a lot of posts saying that is not recommended by Microsoft.
See, the way Sharepoint stores data is a pain. All user data is stored in a table called "AllUserData", which is a big table of hundreds of columns. This table has some important columns for example, tp_ListId (the list which the data row belongs to), tp_RowOrdinal (this one is fun!) and a long list of different nvarchar(s), ntext(s), etc.
Whenever we create a list, there is a new entry added to dbo.AllLists table, which itself has a tp_Fields column. This column is of text datatype, but within it, stores the list of fields of that list in an XML format. If you break each of these fields by a line break, you can see that each of these field in the XML string has attributes such as Name, DisplayName (if the column is a user-defined one), ColName (the actual column name where Sharepoint stores the data).
For example, generally Title column has the ColName of "nvarchar1", so what that means is Sharepoint will store the Title value in "nvarchar1" column of AllUserData table! And if we have ten columns with nvarchar type, Sharepoint will store the first eight in one row with "RowOrdinal" as 0, and then the next in another row with "RowOrdinal" as 1.
While that might be smart for those who created Sharepoint, it's a pain for us, developers or business analysis to talk to the database because we will have no idea which field (that we created) a column refers to.
In addition, just for more confusions, a Site that we see in Sharepoint interface might not be a "Site" in database world. If you open up dbo.Webs table, that stores all the so called "Sites" we created in Sharepoint interface. In reality, they are treated as "Web" in the data world. I guess, from the Sharepoint front-end side of view, a site stays in a Site Collection (which is the real site stored in the database), and the database just stores "Site" as "web".