Posted by: Shantham | March 23, 2009

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.

Read more….

Advertisements

Responses

  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!

  4. Hi Shantha Kumar

    Where in my server can i go to look to see the connection details for my MOSS, i have recently joined a team but the previous guys left no documentation.. and i am new to SharePoint

  5. Hello Shantakumar.
    Can we get the URL of the LIst, given List ID. some thing that can some how link up to the GUI List?
    I know its far flung. But wanted to make sure it is not possible.
    THanks
    Ram

  6. Hi,

    Good post, I am trying to retrieve all the forms libraries that are created under web application by querying content database. can we get all files by using.xsn extension query.

    Thanks,
    Pallela


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: