SharePoint Database SQL Query Tips3

Now, we are going to retrive the file details from AllDocs Database. Which has the informations about the files stored in SharePoint List or Library.

— Returns all document from all lists availabe in WebApplication
SELECT AllDocs.Leafname AS FileName’, 
                 AllDOcs.Dirname AS ‘Folder Path’, 
                 AllLists.tp_Title AS ‘List Title’,
                 Webs.Title AS ‘Web Title’
FROM AllDocs
JOIN AllLists
ON
AllLists.tp_Id=AllDocs.ListId
JOIN Webs
ON
Webs.Id=AllLists.tp_WebId
ORDER BY webs.title

If you need the file informations about particular document type. Use the Extension column to check the document type.

For Ex., The following Query returns only master pages on all WebSites,

— Returns master pages in WebApplication for all WebSites
SELECT AllDocs.Leafname AS FileName’, 
                 AllDocs.Dirname AS ‘Folder Path’, 
                 AllLists.tp_Title AS ‘List Title’,
                 Webs.Title AS ‘Web Title’
FROM AllDocs
JOIN AllLists
ON
AllLists.tp_Id=AllDocs.ListId
JOIN Webs
ON
Webs.Id=AllLists.tp_WebId
WHERE Extension=’master’
ORDER BY Webs.Title

Like the above query, we can get all type of documents.

Shantha Kumar
Shantha Kumar
Articles: 280

Related Posts

24,849 Comments

  1. Hi there, got to your site and was looking at the Query tips. Is it possible for us to retrieve information of files stored in document library too? (as I read it somewhere that doc lib is not quite like a visible database, its smt ghostly)
    so I assume I cant use the above tips?

    Anyway im using SPQUERY on C#

  2. Yes, you can get the files stored under Document Library by using the following Query.

    select AllLists.tp_Title as ‘List Title’, AllDocs.LeafName as ‘File Name’, AllDocs.DirName as ‘File Path’ from AllLists JOIN AllDocs on AllDocs.ListId = AllLists.tp_ID Where AllLists.tp_Title=’Document Library’

    Yes, this is not visible what we can see in GUI or Programming.
    But We can query the Sql whenever we want to check about the details about SharePoint Storage.

  3. alright thanks for the reply.
    got a question that im kinda stuck, hope you can give me some advice

    now that im able to get the files that is under this folder (terminal shift) ONLY

    but under this terminal shift folder i got 7 other folders (BT, KT, and etc)

    and under lets say BT, i got 5 other folders (shift details, shift report and etc)

    inside shift details there are files like shiftdetails200904011.doc, shiftdetails200904012.doc, shiftdetails200904021.doc, shiftdetails200904022.doc

    how can i seperate the .doc with 1’s and 2’s ultimately?

    any idea? thanks in advance!

Comments are closed.