SharePoint 2007 - SQL Query to find a list of documents in site collection
sharepoint, sql
Solution
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.
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.
Problem
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) 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?