Get data of Sharepoint list from content database using SQL Server Query.

There Are two tables in content Database where we retrieve a list data.

• AllLists
• AllUserData

AllLists

An AllLists table in a content DB stores the information when a list is created. It might be very complex to describe every column, but if we get to know some important columns then we will certainly understand what is happening.

Columns

• tp_ID [uniqueidentifier]
  
  To unquely identify the list
   
• tp_WebId [uniqueidentifier]
  
  To identify the web the list belongs to. It is the value taken from the "AllWebs" table that has the Web Id in the "Id" column (tp_ID and tp_WebId together form a primary key for the "AllLists" table).
   
• tp_Title [nvarchar]
  
  To store title of the list
   
• tp_BaseType [int]
  
  To store the base type of the list
  (Generic List: 0, Document Library: 1, Discussion Board:3, Survey:4,Issue:5)
   
• tp_ServerTemplate[int]
  
  To store the type of the list(list type id)
  (ex:100-Generic list,101-Document library,102-Survey,103-Links list,104-Announcements list,
  105-Contacts list,106-Events list, 107-Tasks list,108-Discussion board,109-Picture library)
   
• tp_Fields [varbinary]
  
  To store fields of list in encoded format
   
• tp_ContentTypes [varbinary]
  
  To store content type of list in encoded format
  
  A new custom list "Employee" is created. Now we will see the values of the "AllLists" table
• 
  
   
• tp_ID "4F3F3527-DA03-4057-9FCB-19774C746DC2" is the identifier of the "Employee" list, any item in this list will be saved in the "AllUserData" table along with the list id (tp_ID) value

AllUserData

The AllUserData table in the content db is the single table to store items of any list. This table has 192 columns and all the list items including their history is saved in this table. Documents uploaded to an item or documents in the document library are also saved in this table.

The primary key (non clustered) of this table consists of 7 columns.

Columns

• tp_ID[int]
  
  To identify the list item
   
• tp_GUID[uniqueidentifier]
  
  To uniquely identify the list tiem
   
• tp_ListId [uniqueidentifier]
  
  To identify which list the item belongs to; it is the value of "tp_ID" in the "AllLists" table
   
• tp_SiteId [uniqueidentifier]
  
  To identify which site the item belongs to; it is the value of "Id" in the "AllSites" table
   
• tp_RowOrdinal [tinyint]
  
  zero based ordinal index in the set of rows representing the list item
   
• tp_Author [int]
  
  user identifier who created the list item
   
• tp_Editor [int]
  
  user identifier who last edited the list item
   
• tp_Modified [datetime]
  
  The date and time (in UTC format) when this list item was last modified
   
• tp_Created [datetime]
  
  The date and time (in UTC format) when this list item was created
   
• tp_DeleteTransactionId [varbinary]
  
  An identifier for the implementation specific deleted items in the recycle bin. This will be equal to 0x if the list item is not deleted.
   
• tp_IsCurrentVersion [bit]
  
  bit representing if a row belongs to a current version or an historical version of the list item. This value must be 1 if this row contains a current version else it will be 0
• nvarchar1..64 [nvarchar]
  
  Columns navarchar1, navarchar2….navarchar64 stores values of application fields of type nvarchar. The 64 columns are named nvarchar1 to nvarchar64. If the column does not contain data then this value will be NULL.
   
• ntext1..32 [ntext]
  
  Columns that store values of application fields of type ntext. The 32 columns are named ntext1 to ntext32. If the column does not contain data then this value will be NULL.
   
• bit1..16[bit]
  
  Columns that store values of application fields of type bit. The 16 columns are named bit1 to bit16. If the column does not contain data then this value will be NULL.
   
• datetime1..8 [datetime]
  
  Columns that saves values of application fields of type datetime. The 8 columns are named datetime1 to datetime8. If the column does not contain data then this value will be NULL.
   
• float1..12 [float]
  
  Columns that save values of application fields of type float. The 12 columns are named float1 to float12. If the column does not contain data then this value will be NULL.
   
• int1..16 [int]
  
  Columns that save values of application fields of type int. The 16 columns are named int1 to int16. If the column does not contain data then this value will be NULL.
   
• sql_variant1..8 [sql_variant]
  
  Columns that save values of application fields of type sql_variant. The eight columns are named sql_variant1 to sql_variant8. If the column does not contain data then this value will be NULL.

A custom list SalesInfo is created.

Its tp_ID value from the AllItems table is 4F3F3527-DA03-4057-9FCB-19774C746DC2

Schema of the custom list "Employee"

"Title" field - single lie of text

"Salary" field - Float



I have created 4 values in the list. The following is the list image:



Now we will check the AllUserData table.

When any user restores from the recycle bin, its tp_DeleteTransactionId will be back to "0x".

Conclusion

We can also write your own SQL queries on this table to get specific list data, but remember any changes to the content DB are not supported by Microsoft. We have to respect the fact that we should not modify a content DB. When you write a CAML query on any list, internally it will execute a SQL query on this table with conditions.

If you have planned to write custom queries on this table, you should be well aware of the where conditions as there is no specific document explaining the internals of the content DB.

Hope you enjoyed the article to understand what is happening when a list and item are created, though the intention is not to modify the content DB.