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

Question

I need to get a list of all the documents in the site collection, which I think I can do with either the alldocs table or the alluserdata table (MOSS 2007 SP1), but I don’t see how I can get information about the author of the document. I do not need the contents of the document (for example, the contents of AllDocStreams)

Something like that:

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 do this?

Answer 1

Never query a SharePoint database directly. This is completely unsupported, and you may encounter difficulties in moving forward (for example, if a service pack or hotfix changes the scheme, then the application does not work).

Answer 2

Why not use the sharepoint object model and then use the raw database method? I know that the object model approach has a performance limit compared to the database, but MS can change the db schema to the next path. On the other hand, the likelihood that MS will violate its own object model is much less, and as far as I know, the recommended way is to use either the object model or web services.

Answer 3

People who claim that you cannot query SharePoint databases because they are not supported are incorrect. From reading the documentation, you can query the database while you use the With (NoLock) clause. It is clearly not supported for updating, deleting, or inserting records.

The following query is supported:

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

I will send a request that will give the desired result in a few minutes.

Answer 4

Below are the first 100 largest documents that have been 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

Five reasons to not directly access SharePoint databases http://www.sharepoint4arabs.com/AymanElHattab/Lists/Posts/Post.aspx?ID=99

Answer 6

I recommend that you take a look at the Camelot.NET connector, which allows you to query SharePoint 2007/2010 using standard SQL queries. Its driver is ADO.NET, which can also be opened through a simple WCF service and is accessible through any programming language. Suppose you would like to choose from the "general documents", you should write something like:

 select * from `shared documents` 

or with specific columns:

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

or with the where statement:

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

Answer 7

• Why don't you use the Content Query Web Part ?
• Why don't you use a search object to query the same? That would be my preferred solution. Search has most of the properties already, and you can add more if you need them. Searching is probably much faster than querying content databases.

Whether this is supported or not, it’s still a bad form of querying the content database directly, and any developer who would suggest this as a solution should get a lecture;). For example, what happens if an administrator creates a second content database for your webapp? If the query is executed through site collections, it will not return the desired results until you specify it in the code.

Answer 8

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

In this particular case, I think that getting a list of files from a MOSS document library using the SharePoint web service would help. If this is not your exact scenario, it will return you to the right path.

If the document service does not help you, the search service, I am sure. Check the documentation for use.

Answer 9

You can get some information from the UserInfo table by attaching AllUserData.tp_Author to UserInfo.tp_ID, but the clutter in these tables is not recommended and can be very fragile, and your requests do not guarantee operation after applying any patches or service packs for SharePoint. I would use web services or the SharePoint object model to access data.