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.

3 thoughts on “SharePoint Database SQL Query Tips3

  1. stenloves Reply

    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. Shantha Kumar Reply

    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. stenloves Reply

    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!

Leave a Reply

Your email address will not be published. Required fields are marked *