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

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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).

Answer 4

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

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

Answer 5

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:

select * from `shared documents`

or with certain columns:

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

or with where statement:

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

Answer 6

• Why don't you use a Content Query web part?
• Why don't you use a search object to 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).

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.

Answer 7

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

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.

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

Answer 8

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.