如何從SharePoint Content DB中查詢List資料

SharePoint用來維護基礎資料非常方便，只需要建立自定義列表，然後使用InfoPath自定義一下維護介面，就可以實現線上的增刪改查，開發效率很高。如果維護的資料需要進行審批，還可以加入工作流功能。使用SharePoint Designer可以快速開發出簡單的工作流，如果是很複雜的工作流，那麼就需要使用VS進行開發了。現在資料已經維護進了SharePoint List，那麼怎麼從資料庫中將維護的資料查詢出來呢？

SharePoint 的列表資料都儲存在Content DB中，其中最最重要的表就是[dbo].[AllUserData]，這個表中的一行資料就對應SharePoint List中的一條資料。下面介紹下如何從Content DB中查詢出List資料。

Case 1簡單資料型別的自定義列表查詢

假設我們現在有一個Country列表，記錄了全球200多個國家和地區的中文名，英文名，建國日期，面積，人口等資訊，整個列表只有字串、日期、數字等簡單型別，沒有User，Lookup等資料型別，則整個List的資料都可以從[dbo].[AllUserData]查詢獲得，具體查詢步驟：

1. 查詢[dbo].[AllLists]找到ListId。

select *
from [dbo].[AllLists]
where tp_Title = 'Country'

由於整個網站集都是共用一個Content DB資料庫，所以可能會出現在多個網站中都建立了Country這個List的情況，那麼就會返還多條結果，這個情況下，就需要關聯AllWebs表，根據網站的Url來判斷到底哪個ListId才是我們需要的。

select w.FullUrl,l.tp_ID
from [dbo].[AllLists] l
inner join [dbo].[AllWebs] w
on l.tp_WebId=w.Id
where l.tp_Title = 'Country'

在找到了ListId後，接下來所有查詢都會用到這個Id。

2. 查詢[dbo].[AllUserData]，找到需要查詢的列，並命名為別名。

假設第一步我們查詢出來的ListId是'F20E316B-EA24-4164-9437-BBB17C182691'，那麼我們查詢Country這個列表的所有資料的SQL就是：

SELECT *
FROM AllUserData
where tp_ListId='F20E316B-EA24-4164-9437-BBB17C182691'

這個表的列非常多，在SharePoint 2013的環境中會更多，但是儲存資料的列都是用“資料型別+數字”來命名的。所以如果要找建國日期這個欄位對應的列，那麼就去看datetime1 datetime2等，如果要找面積，人口等數值型別的列，那就去看float1 float2等；如果要找中文名，英文名之類的字串列，那就要看nvarchar1 nvarchar3等列。這裡基本上都是靠眼睛來看的，根據查詢的結果推斷哪些欄位儲存了哪些資料。在得知每個欄位的對應後，即可修改查詢，將別名加上。

SELECT d.nvarchar1 as ChineseName,
d.nvarchar3 as EnglishName,
d.datetime1 as FoundingDate,
d.float1 as Area,
d.float2 as Population
FROM AllUserData d
where tp_ListId='F20E316B-EA24-4164-9437-BBB17C182691'

【注意：SharePoint並沒有在資料庫中以很方便的結構展現哪些列表欄位對應哪個資料庫欄位，在AllLists表中，雖然有個欄位tp_Fields，但是在SharePoint2010及之後，該欄位是壓縮的二進位制，使用SQL是無法讀取的。所以根本不可能通過查詢資料庫得知哪個欄位的別名是什麼。】參考：http://stackoverflow.com/questions/8988098/how-could-i-find-the-fields-of-a-sharepoint-list-from-database-in-sharepoint-201

3. 過濾掉已刪除的資料。

如果資料經過刪除，然後又重新錄入，那麼我們就會發現，第2步的查詢結果會把刪除的和重新錄入的資料都查詢出來。SharePoint採用的刪除方法都是軟刪除，通過設定一個標誌位來表示一條資料已經被刪除，所以我們只需要將刪除標識tp_DeleteTransactionId=0新增到where條件中，即可將未刪除的資料返回。

SELECT d.nvarchar1 as ChineseName,
d.nvarchar3 as EnglishName,
d.datetime1 as FoundingDate,
d.float1 as Area,
d.float2 as Population
FROM AllUserData d
where tp_ListId='F20E316B-EA24-4164-9437-BBB17C182691' and d.tp_DeleteTransactionId=0

4. 過濾掉歷史版本的資料。

如果這個列表開啟了版本控制，那麼我們查詢的結果可能包含多個版本的資料，而我們只需要最新版本的資料，不希望歷史版本資料出現在查詢中。AllUserData表中，使用tp_IsCurrentVersion欄位來標識這條資料是最新的當前版本還是歷史版本。

於是，查詢最新版本的SQL改為：

SELECT d.nvarchar1 as ChineseName,
d.nvarchar3 as EnglishName,
d.datetime1 as FoundingDate,
d.float1 as Area,
d.float2 as Population
FROM AllUserData d
where tp_ListId='F20E316B-EA24-4164-9437-BBB17C182691' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 

5. 過濾掉內容審批未通過的資料。

如果這個列表開啟了內容審批，那麼就會出現多個最新版本的情況，一個是已經被審批通過的版本，另一個是修改後還沒有進行審批的版本。一般來說，我們是希望只有審批通過的才查詢出來，使用者進行修改後，只要審批狀態不是Approve，那麼就不應該出現在查詢結果中。在AllUserData表中，使用tp_ModerationStatus欄位來標識這行資料是否已經被審批通過。這是一個列舉型別，其值為：

0 The list item is approved.
1 The list item has been denied approval.
2 The list item is pending approval.
3 The list item is in the draft or checked out state.
4 The list item is scheduled for automatic approval at a future date.

這裡，我們只要審批通過的資料，所以我們的SQL更新為：

SELECT d.nvarchar1 as ChineseName,
d.nvarchar3 as EnglishName,
d.datetime1 as FoundingDate,
d.float1 as Area,
d.float2 as Population
FROM AllUserData d
where tp_ListId='F20E316B-EA24-4164-9437-BBB17C182691' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0 

Case 2引用（Lookup）其他列表的自定義列表查詢

若要將關係資料維護到SharePoint中，那麼Lookup資料型別是非常常用的實現方法。使用Lookup可以很容易實現一對多和多對多關係，關於多對多關係，我們在Case3中再進行講解，先看看一對多關係的維護與查詢。

緊接著Case1，現在我們需要建立一個使用者表，裡面記錄了使用者的姓名，生日，出生國等資訊，出生國欄位對應的就是Lookup Country這個List，使用者出生國不能亂填，必須從現有Country中進行選擇，而且只能選擇一個，這就是典型的一對多關係。

做過資料庫設計的都應該知道，對於一對多關係，需要使用一個表新增另一個表的主鍵來作為外來鍵，如果是資料庫表的話，那麼User表中必然有個BirthCountryId列。那麼在SharePoint中，所有列表都儲存在AllUserData表中，那這種關係怎麼表示呢？

AllUserData表中有很多int1 int2之類的整型欄位，但是這些欄位並不用於儲存數值型別（數值型別使用float1 float2等儲存），而是用於儲存Lookup表的外來鍵。查詢具有Lookup欄位的自定義列表的操作如下：

1. 查詢外來鍵表。

這裡Country表是User表的外來鍵表，所以先將Country表查出來，查的欄位必須包含tp_ID，這個整型主鍵值就是用作外來鍵關聯的。

SELECT d.tp_ID,
d.nvarchar1 as ChineseName,
d.nvarchar3 as EnglishName,
d.datetime1 as FoundingDate,
d.float1 as Area,
d.float2 as Population
FROM AllUserData d
where tp_ListId='F20E316B-EA24-4164-9437-BBB17C182691' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0 

2. 查詢主表。

對於我們的主表User表，查詢方法跟Case1的步驟一樣，找到ListId，找到需要的欄位，同時需要找到外來鍵的關聯欄位，也就是int1 int2這種欄位。

SELECT 
d.nvarchar1 as UserChineseName,
d.nvarchar3 as UserEnglishName,
d.datetime1 as Birthdate,
d.int1 as CountryId
FROM AllUserData d
where tp_ListId='53B70F07-3A66-4947-8560-05C5CCCE6A21' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0 

3. Join兩個查詢結果。

現在看來，前兩步的查詢就像是兩個獨立的表一樣了，那麼接下來我們只需要把主表和外來鍵表進行關聯查詢，即可。比如我們需要查詢使用者的姓名，生日，出生地國家中文名，那麼對應的SQL就是：

SELECT 
d.nvarchar1 as UserChineseName,
d.nvarchar3 as UserEnglishName,
d.datetime1 as Birthdate,
c.ChineseName as BirthCountryChineseName
FROM AllUserData d
inner join 
(
SELECT d.tp_ID,
d.nvarchar1 as ChineseName,
d.nvarchar3 as EnglishName,
d.datetime1 as FoundingDate,
d.float1 as Area,
d.float2 as Population
FROM AllUserData d
where tp_ListId='F20E316B-EA24-4164-9437-BBB17C182691' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0 
) c
on d.int1=c.tp_ID
where tp_ListId='53B70F07-3A66-4947-8560-05C5CCCE6A21' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0 

當然，我們可以對這個查詢進行簡化，把不需要查詢返回的欄位都刪掉。

Case 3引用其他列表並可多選的自定義列表查詢

前面只是說的一對多情況的關係，但是要在關聯式資料庫中實現多對多關係，那麼就需要用到中間表。同樣的道理，在SharePoint中，Lookup欄位可以設定成多選，那麼就變成了多對多關係，對應資料庫中使用[dbo].[AllUserDataJunctions]這個表來實現多對多關聯。

我們還是用Case2中的使用者表和國家表舉例，如果我們為使用者表新增國籍欄位，由於有些國家允許雙重國籍，所以使用者和國家就變成了多對多關係。與一對多中使用tp_ID進行關聯不一樣，在多對多關聯中，使用的是主表的Doc_Id和Lookup表的tp_ID進行關聯。查詢步驟如下：

1. 查詢被Lookup的表。同Case2，不再累述。

2. 查詢主表，需要tp_DocId欄位。查詢雷同Case2，只是需要多新增一個tp_DocId欄位。

3. 查詢中間表，主要是tp_DocId和tp_Id兩個欄位。

中間表的聯合主鍵欄位較多，where條件比較複雜，如果主表和Lookup的表只存在一個多對多關係，那麼我們可以寫成：

select tp_DocId,tp_Id
from [dbo].[AllUserDataJunctions]
where tp_SourceListId='53B70F07-3A66-4947-8560-05C5CCCE6A21' and tp_DeleteTransactionId=0 and tp_IsCurrentVersion=1

如果主表和Lookup表存在多個多對多關係，那麼我們還需要知道這裡要查詢的多對多是哪個欄位的多對多。關於FieldId，並不能很簡單的通過介面看到，我只找到通過程式碼或者SPCAMLQueryHelper這樣的第三方工具才能檢視。在得知了FieldId後，我們的查詢便可改為：

select tp_DocId,tp_Id
from [dbo].[AllUserDataJunctions]
where tp_SourceListId='53B70F07-3A66-4947-8560-05C5CCCE6A21' and tp_DeleteTransactionId=0 and tp_IsCurrentVersion=1 and tp_FieldId='48DCB501-EBFD-4727-85AE-9CC9A51E73CF'

4. 聯合查詢主表、Lookup表和中間表。

前面三個步驟我們已經得到了三個獨立的表查詢，接下來我們就可以按照普通的三個表進行Join查詢的方式，將結果查詢出來。

select main.*,lkup.ChineseName as Nationality
from
(
SELECT d.tp_DocId,
d.nvarchar1 as UserChineseName,
d.nvarchar3 as UserEnglishName,
d.datetime1 as Birthdate
FROM AllUserData d
where tp_ListId='53B70F07-3A66-4947-8560-05C5CCCE6A21' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0 
)main
inner join
(
select tp_DocId,tp_Id
from [dbo].[AllUserDataJunctions]
where tp_SourceListId='53B70F07-3A66-4947-8560-05C5CCCE6A21' and tp_DeleteTransactionId=0 and tp_IsCurrentVersion=1 and tp_FieldId='48DCB501-EBFD-4727-85AE-9CC9A51E73CF'
)m2m
on main.tp_DocId=m2m.tp_DocId
inner join
(
SELECT d.tp_ID,
d.nvarchar1 as ChineseName,
d.nvarchar3 as EnglishName,
d.datetime1 as FoundingDate,
d.float1 as Area,
d.float2 as Population
FROM AllUserData d
where tp_ListId='F20E316B-EA24-4164-9437-BBB17C182691' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0 
)lkup
on m2m.tp_Id=lkup.tp_ID

【注意：這裡使用的都是Inner Join，那是因為我們認為多選的Lookup是必選的，最少需要選一個，如果是允許不選的，那麼就需要改寫為Left Join。】

Case 4包含使用者或使用者組型別的自定義列表使用者查詢

在SharePoint自定義列表的資料型別中，除了前面Case提到的基本資料型別和Lookup型別外，還有比較常用的就是Person or Group型別。SharePoint Content DB的User資料，儲存在[dbo].[UserInfo]表中，在AllUserData中，只需要儲存User的ID（int型別）即可。UserId和Lookup表一樣，是佔用的int4 int5這樣的int型別的列，但具體是int幾那需要根據實際情況用肉眼去看。另外在AllUserData中有兩個常用的UserId，那就是tp_Author和tp_Editor，表示該行資料的建立人和修改人。

關於UserInfo表需要注意的是，這個表的主鍵是[tp_SiteID],[tp_ID]，也就是說只通過UserId去Join的話，可能會找到多條資料，在不同的Site中，UserId是可能重複的。

還是以Case2的使用者為例，我們希望知道每條使用者資料最後是誰編輯的，將編輯者的登入名顯示出來。那麼我們需要進行如下操作：

1. 查詢使用者表，幷包含tp_Editor列。與Case2相似，這個不再累述。

2. 查詢UserInfo表，將該使用者表所在的Site作為過濾條件。

關於SiteId，我們可以在AllUserData中找到，就是tp_SiteID欄位。

select tp_ID,tp_Login
from [dbo].[UserInfo] u
where u.tp_SiteID='C4994C7F-ABEF-4D61-9126-086EBE8AE4D5'

3. 聯合查詢使用者表和UserInfo表，將編輯者的登入名新增到查詢結果中。

SELECT 
d.nvarchar1 as UserChineseName,
d.nvarchar3 as UserEnglishName,
d.datetime1 as Birthdate,
d.int1 as CountryId,
users.tp_Login as EditorLoginName
FROM AllUserData d
inner join
(
select tp_ID,tp_Login
from [dbo].[UserInfo] u
where u.tp_SiteID='C4994C7F-ABEF-4D61-9126-086EBE8AE4D5'
) users
on d.tp_Editor=users.tp_ID
where tp_ListId='53B70F07-3A66-4947-8560-05C5CCCE6A21' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0 

Case 5包含使用者或使用者組型別的自定義列表使用者組查詢

Case4這裡只是查詢了使用者，如果我們新增的是一個使用者組的資料，那麼就不能再從UserInfo表中進行查詢，而是要從[dbo].[Groups]中進行查詢。

Groups表的主鍵和UserInfo很相似，也是[SiteId],[ID]。而且Groups表和UserInfo表的ID使用的是同一套Sequence。也就是說，如果對於某一個SiteId，我們在UserInfo表中有1,2,3這三個ID的使用者，那麼我們肯定在Groups中找不到同樣ID的Group，GroupID和UserID是不會重複的，這樣就避免了在關聯AllUserData時Confused。

AllUserData表中並沒有一個標識說關聯的到底是UserInfo表還是Groups表，所以我們在查詢時，可能需要將Groups的資料和UserInfo的資料Union起來然後在和AllUserData進行Join查詢。

更普遍的情況是，我們其實並不關心Group的資訊，我們更希望得到的是AllUserData和GroupMember的列表，這個時候我們就還需要Join [dbo].[GroupMembership]表。

以使用者資料表為例，假設我們新增了一個使用者或使用者組的列“審批人”用以表示該使用者的資料由哪些人審批。該列填入的資料都是SharePoint中建立的使用者組。那麼我們要查詢出使用者的審批人列表，那麼操作如下：

1. 查詢使用者資料。這裡需要關注的是int型別的列，審批人這個欄位就儲存在int列中。

2. 查詢Groups表和GroupMembership表，獲得使用者組的資訊。當然，這裡也需要Join到UserInfo表，找到真正的Membership的登入名。

select g.ID,u.tp_Login
from [dbo].[Groups] g
inner join [dbo].[GroupMembership] gm
on g.ID=gm.GroupId and g.SiteId=gm.SiteId
inner join [dbo].[UserInfo] u
on u.tp_ID=gm.MemberId and u.tp_SiteID=gm.SiteId
where g.SiteId='C4994C7F-ABEF-4D61-9126-086EBE8AE4D5'

3. 將步驟1、2的查詢結果進行Join，便可得到使用者的審批人。

SELECT 
d.nvarchar1 as UserChineseName,
d.nvarchar3 as UserEnglishName,
d.datetime1 as Birthdate,
d.int1 as CountryId,
users.tp_Login as Approvers
FROM AllUserData d
left join
(
select g.ID,u.tp_Login
from [dbo].[Groups] g
inner join [dbo].[GroupMembership] gm
on g.ID=gm.GroupId and g.SiteId=gm.SiteId
inner join [dbo].[UserInfo] u
on u.tp_ID=gm.MemberId and u.tp_SiteID=gm.SiteId
where g.SiteId='C4994C7F-ABEF-4D61-9126-086EBE8AE4D5'
) users
on d.int4=users.ID
where tp_ListId='53B70F07-3A66-4947-8560-05C5CCCE6A21' and d.tp_DeleteTransactionId=0 and d.tp_IsCurrentVersion=1 and tp_ModerationStatus=0 

以上介紹了關於基本型別，Lookup型別，使用者和組型別的SharePoint資料庫查詢方法，對於其他的資料型別都可以當做基本型別來對待：

• Multiple Text:ntext型別
• Choose：nvarchar型別
• Currency：float型別
• Yes/No：bit型別
• Hyperlink or Picture：nvarchar型別
• Calculated：看具體選擇的結果型別
• External Data：nvarchar ntext都會用到
• Managed Metadata：未研究

以上幾個Case的查詢已經包含了大部分資料查詢的情況，對於不同的資料列表，只需要稍作修改即可從SharePoint資料庫中查詢維護的列表。