SharePoint Directly Query Database

Query Documents:

dbo.AllDocs is a table that holds information about all the documents in all document libraries for all sites and sub sites. Here is simple query to retrieve documents from "Shared Documents" library from all sites. 



SELECT *
FROM AllDocs 
JOIN AllLists 
ON AllLists.tp_Id=AllDocs.ListId
JOIN Webs
ON Webs.Id=AllLists.tp_WebId
 where 
 AllLists.tp_Title = 'Shared Documents'
 ORDER BY webs.title


The above query will return all documents including System Forms like "EditForm.aspx". In order to exclude these documents executue query below:



SELECT *
FROM AllDocs 
JOIN AllLists 
ON AllLists.tp_Id=AllDocs.ListId
JOIN Webs
ON Webs.Id=AllLists.tp_WebId
 where 
 AllLists.tp_Title = 'Shared Documents' and
 AllDOcs.Dirname not like '%/Forms%' and
 AllDocs.LeafName !='Forms'
 ORDER BY webs.title


To execute query on particular web add another condition in where clause "webs.Title='title of web'" as below:

SELECT *
FROM AllDocs 
JOIN AllLists 
ON AllLists.tp_Id=AllDocs.ListId
JOIN Webs
ON Webs.Id=AllLists.tp_WebId
 where 
 AllLists.tp_Title = 'Shared Documents' and
 AllDOcs.Dirname not like '%/Forms%' and
 AllDocs.LeafName !='Forms' and
 webs.title = 'Development'


To get detail about user contribution we can use query:




SELECT  count(*) as 'Number Of Documents & Items', UserInfo.tp_Title
FROM AllDocs, AllLists, Webs,UserInfo
where AllLists.tp_Id=AllDocs.ListId and
Webs.Id=AllLists.tp_WebId and
UserInfo.tp_SiteID = Webs.SiteId and
UserInfo.tp_ID = tp_Author and
webs.title = 'Development'
Group by UserInfo.tp_Title






Note:

1. All these queries are completed unsupported.
2. Direct SELECT statements against the database take shared read locks at the default transaction level so your custom queries might cause deadlocks and stability issues. 

To overcome stability issue

To overcome stability issue when we directly query database we can use With (NoLock).  

Select * From dbo.AllDocs With (NoLock)