Thursday, December 10, 2009

Finding the Elusive Lookup Field with SQL Query against Sharepoint's Backend Database

Thursday, December 10, 2009
Because Sharepoint data is housed in SQL, it's possible to query it using T-SQL (example here ).  Of course, M$ says you shouldn't query the database directly, as it could impact performance, schema could suddenly change with a sharepoint update, blah blah blah.  There ARE supported ways in which to query Sharepoint data in SQL, such as Sharepoint List Association Manager on codeplex.

Finding the data isn't too hard.  But the other day I encountered a list with fields of type "lookup" in sharpeoint, and realized that lookup fields are handled differently in the database.  You can't just look at the UserData table fields like you can with other types of data.

Lookup data requires a JOIN between UserData and AllUserData tables on the UserData.intX and AllUserData.tp_ID fields for the corresponding list in AllUserData.  So, I used the concept of querying Sharepoint list data in SQL and added the following steps to the process:
Find the proper tp_ListId in AllUserData by looking for the List Name in tp_DirName.  This value is not going to be the same as the tp_ListId value in UserData because "lookup" means it's two different lists ;)
Review the UserData table int fields and find the one that corresponds to the AllUserData.tp_ID, and create your table join here.
In my case, the value I needed was stored in UserData.int7:
SELECT     WSS_Content.dbo.AllUserData.nvarchar3 AS EmployeeName
FROM     WSS_Content.dbo.UserData INNER JOIN WSS_Content.dbo.AllUserData ON WSS_Content.dbo.UserData.int7 = WSS_Content.dbo.AllUserData.tp_ID
WHERE     (WSS_Content.dbo.UserData.tp_ListId = 'value of UserData.tp_ListId') AND (WSS_Content_TServer.dbo.AllUserData.tp_ListId = 'value of AllUserData.tp_ListId')