SharePoint 2007 - 用于在网站集中查找文档列表的 SQL 查询

SharePoint 2007 - SQL Query to find a list of documents in site collection

提问by PapaDaniel

I need to get a list of all documents in a site collection, which I believe I can do with either the alldocs table or the alluserdata table (MOSS 2007 SP1) but do not see how I can get the author information for the document. I do not need the contents of the document (e.g. AllDocStreams content)

我需要获取网站集中所有文档的列表，我相信我可以使用 alldocs 表或 alluserdata 表 (MOSS 2007 SP1) 来完成，但看不到如何获取文档的作者信息。我不需要文档的内容（例如 AllDocStreams 内容）

Something like this:

像这样的东西：

SELECT     tp_DirName, tp_LeafName, tp_Version, tp_Modified, tp_Created
FROM         AllUserData
WHERE     (tp_ContentType = 'Document') 
AND (tp_LeafName NOT LIKE '%.css') 
AND (tp_LeafName NOT LIKE '%.jpg') 
AND (tp_LeafName NOT LIKE '%.png') 
AND (tp_LeafName NOT LIKE '%.wmf') 
AND (tp_LeafName NOT LIKE '%.gif') 
AND (tp_DirName NOT LIKE '%Template%') 
AND (tp_IsCurrentVersion = 1) 
AND (tp_LeafName NOT LIKE '%.xsl')
ORDER BY tp_SiteId, tp_ListId, tp_DirName, tp_LeafName, tp_IsCurrentVersion DESC

Is there a better way to go about this?

有没有更好的方法来解决这个问题？

回答by

People that claim that you cannot query SharePoint databases because it is not supported are wrong. From reading the documentation, it is fine to query the database as long as you use the 'With(NoLock)' clause. It is clearly not supported to update, delete, or insert records.

声称您无法查询 SharePoint 数据库因为它不受支持的人是错误的。通过阅读文档，只要使用“With(NoLock)”子句就可以查询数据库。显然不支持更新、删除或插入记录。

The below query is supported:

支持以下查询：

Select * 
From your_content_database.dbo.AllDocs With (NoLock)

I will post a query that provides the desired result in a few minutes.

我将在几分钟内发布一个提供所需结果的查询。

回答by Kasper

Why not use the sharepoint object model rather then using the raw database approach? I know that the object model approach does have a performance penalty compared to the database, but MS could change the db schema with the next path. On the other hand the likelyhood of MS breaking their own object model is far less, and as far as I know the recommended way is to use either the object model or the web services.

为什么不使用共享点对象模型而不是使用原始数据库方法？我知道与数据库相比，对象模型方法确实有性能损失，但 MS 可以使用下一个路径更改 db 模式。另一方面，MS 破坏自己的对象模型的可能性要小得多，据我所知，推荐的方法是使用对象模型或 Web 服务。

回答by Kasper

Don't ever query the SharePoint database directly. This is completely unsupported and can get you into trouble moving forward (for instance, if a service-pack or hotfix modifies schema, then you app is broken).

永远不要直接查询 SharePoint 数据库。这是完全不受支持的，可能会给您带来麻烦（例如，如果服务包或修补程序修改了架构，则您的应用程序已损坏）。

回答by Kasper

The below would return the top 100 largest documents that were added in the last 24 hours to the content database.

下面将返回在过去 24 小时内添加到内容数据库的前 100 个最大文档。

Select Top 100 
       W.FullUrl, 
       W.Title, 
       L.tp_Title as ListTitle, 
       A.tp_DirName, 
       A.tp_LeafName, 
       A.tp_id , 
       DS.Content , 
       DS.Size, 
       D.DocLibRowID, 
       D.TimeCreated, 
       D.Size, 
       D.MetaInfoTimeLastModified, 
       D.ExtensionForFile 
From your_content_database.dbo.AllLists L With (NoLock) 
join your_content_database.dbo.AllUserData A With (NoLock) 
  On L.tp_ID=tp_ListId 
join your_content_database.dbo.AllDocs D With (NoLock) 
  On A.tp_ListID=D.ListID 
 And A.tp_SiteID=D.SiteID 
 And A.tp_DirName=D.DirName 
 And A.tp_LeafName=D.LeafName 
join your_content_database.dbo.AllDocStreams DS With (NoLock) 
  On DS.SiteID=A.tp_SiteID 
 And DS.ParentID=D.ParentID 
 And DS.ID=D.ID 
join your_content_database.dbo.Webs W With (NoLock) 
  On W.ID=D.WebID 
 And W.ID=L.Tp_WebID 
 And W.SiteID=A.tp_SiteID 
Where DS.DeleteTransactionID=0x 
  And D.DeleteTransactionID=0x 
  And D.IsCurrentVersion=1 
  And A.tp_DeleteTransactionID=0x 
  And A.tp_IsCurrentVersion=1 
  And D.HasStream=1 
  And L.tp_DeleteTransactionId=0x 
  And ExtensionForFile not in('webpart','dwp','aspx','xsn','master','rules','xoml') 
  And D.MetaInfoTimeLastModified>DateAdd(d,-1,GetDate()) 
Order by DS.Size desc

回答by Ulf

I recommend that you have a look at the Camelot .NET Connector which allows you to query SharePoint 2007/2010 using standard SQL queries. Its a ADO.NET driver that can also be exposed through a simple WCF service and by that available through any programming language. Lets say one would like to select from "shared documents", you would write something like:

我建议您查看 Camelot .NET 连接器，它允许您使用标准 SQL 查询来查询 SharePoint 2007/2010。它是一个 ADO.NET 驱动程序，它也可以通过一个简单的 WCF 服务公开，并且可以通过任何编程语言提供。假设有人想从“共享文档”中进行选择，您可以编写如下内容：

select * from `shared documents`

or with certain columns:

或某些列：

select id, title, filetype, filesize, created, createdby from `shared documents`

or with where statement:

或使用 where 语句：

select id, title, filetype, filesize, created, createdby from `shared documents` where filetype = '.gif'

回答by ArjanP

• Why don't you use a Content Query web part?
• Why don't you use a search objectto query the same? This would be my preferred solution. Search has most properties already and you can add more if you need them. Search is probably a lot quicker than querying content database(s).

• 为什么不使用内容查询 Web 部件？
• 为什么不使用搜索对象来查询相同的内容？这将是我首选的解决方案。搜索已经拥有大多数属性，如果需要，您可以添加更多属性。搜索可能比查询内容数据库要快得多。

Whether it is supported or not, it is still bad form to query the Content Database directly and any developer who would suggest this as a solution should get a lecture ;). For instance, what happens if an admin creates a second content database to your webapp? If you query goes across site collections it will not return the desired results until you provide for this in code.

无论是否支持，直接查询内容数据库仍然是不好的形式，任何建议将此作为解决方案的开发人员都应该接受讲座;)。例如，如果管理员为您的 web 应用程序创建了第二个内容数据库，会发生什么？如果您查询跨网站集，它不会返回所需的结果，直到您在代码中提供此结果。

回答by cciotti

MOSS provides many webservicesout of the box which make life a little easier. They are always worth exploring.

MOSS提供了许多web服务的开箱这也使生活变得更轻松。它们总是值得探索的。

For this particular instance, I think the article, Getting a list of files from a MOSS document library using a SharePoint web service, will be of assistance. If this isn't your exact scenario, it will get you on the right track.

对于此特定实例，我认为使用 SharePoint Web 服务从 MOSS 文档库获取文件列表的文章将有所帮助。如果这不是您的确切情况，它将使您走上正确的轨道。

If the Document service doesn't help you, the Search service will I'm sure. Check the documentation for usage.

如果文档服务对您没有帮助，我相信搜索服务会帮助您。检查文档以了解使用情况。

回答by Cruiser

You can get some of the information from the UserInfo table by joining AllUserData.tp_Author to UserInfo.tp_ID, but messing around in these tables is not recommended and can be very fragile, and also your queries are not guaranteed to work after applying any patches or service packs to SharePoint. I would use either webservices or the SharePoint object model to access the data.

您可以通过将 AllUserData.tp_Author 连接到 UserInfo.tp_ID 来从 UserInfo 表中获取一些信息，但不建议在这些表中乱搞，而且可能非常脆弱，而且在应用任何补丁或服务包到 SharePoint。我会使用 Web 服务或 SharePoint 对象模型来访问数据。