List all Documents in SharePoint

I’ve used these queries for years, they come in handy when planning growth or a migration. A quick Google search will find many more queries to extract information from SharePoint. These work in wss2 and wss3.

Total number of items stored in the list/document library, ordered by the total item count:

SELECT CASE WHEN webs.fullurl = '' 
THEN 'Portal Site' 
ELSE webs.fullurl
END AS [Site Relative Url], 
Webs.Title AS [Site Title], 
CASE tp_servertemplate
WHEN 104 THEN 'Announcement'
WHEN 105 THEN 'Contacts'
WHEN 108 THEN 'Discussion Boards'
WHEN 101 THEN 'Docuemnt Library'
WHEN 106 THEN 'Events'
WHEN 100 THEN 'Generic List'
WHEN 1100 THEN 'Issue List'
WHEN 103 THEN 'Links List'
WHEN 109 THEN 'Image Library'
WHEN 115 THEN 'InfoPath Form Library'
WHEN 102 THEN 'Survey'
WHEN 107 THEN 'Task List'
ELSE 'Other' END AS TYPE,
tp_title 'Title', 
tp_description AS [Description], 
tp_itemcount AS [Total Item]
FROM lists inner join webs ON lists.tp_webid = webs.Id
WHERE tp_servertemplate IN (104,105,108,101, 
	106,100,1100,103,109,115,102,107,120)
ORDER BY tp_itemcount DESC

Total number of documents:

SELECT COUNT(*) 
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE Docs.Type <> 1 
AND (LeafName IS NOT NULL) 
AND (LeafName <> '')
AND (LeafName NOT LIKE '%.stp')
AND (LeafName NOT LIKE '%.aspx')
AND (LeafName NOT LIKE '%.xfp')
AND (LeafName NOT LIKE '%.dwp')
AND (LeafName NOT LIKE '%template%')
AND (LeafName NOT LIKE '%.inf')
AND (LeafName NOT LIKE '%.css')

Total number of Microsoft Word documents:

SELECT COUNT(*)
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE Docs.Type <> 1 
AND (LeafName LIKE '%.doc')
AND (LeafName NOT LIKE '%template%')

List documents by age:

SELECT Webs.FullUrl AS SiteUrl, Webs.Title AS [Title], DirName + '/' + LeafName AS [Document Name], Docs.TimeCreated
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE Docs.Type <> 1 
AND (LeafName IS NOT NULL) 
AND (LeafName <> '')
AND (LeafName NOT LIKE '%.stp')
AND (LeafName NOT LIKE '%.aspx')
AND (LeafName NOT LIKE '%.xfp')
AND (LeafName NOT LIKE '%.dwp')
AND (LeafName NOT LIKE '%template%')
AND (LeafName NOT LIKE '%.inf')
AND (LeafName NOT LIKE '%.css')
ORDER BY Docs.TimeCreated DESC

Size of files ordered by file size:

SELECT TOP 100 Webs.FullUrl AS SiteUrl, Webs.Title AS [Title], DirName + '/' + LeafName AS [Document Name], CAST((CAST(CAST(Size AS DECIMAL(10,2))/1024 AS DECIMAL(10,2))/1024) AS DECIMAL(10,2)) AS 'Size in MB'
FROM Docs INNER JOIN Webs ON Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE Docs.Type <> 1 
AND (LeafName IS NOT NULL) 
AND (LeafName <> '')
AND (LeafName NOT LIKE '%.stp')  
AND (LeafName NOT LIKE '%.aspx') 
AND (LeafName NOT LIKE '%.xfp') 
AND (LeafName NOT LIKE '%.dwp') 
AND (LeafName NOT LIKE '%template%') 
AND (LeafName NOT LIKE '%.inf') 
AND (LeafName NOT LIKE '%.css') 
ORDER BY 'Size in MB' DESC

Total size of all files stored in SharePoint:

SELECT SUM(CAST((CAST(CAST(Size AS DECIMAL(10,2))/1024 AS DECIMAL(10,2))/1024) AS DECIMAL(10,2))) AS 'Total Size in MB'
FROM Docs INNER JOIN Webs ON Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE Docs.Type <> 1
AND (LeafName IS NOT NULL)
AND (LeafName NOT LIKE '%.stp')
AND (LeafName NOT LIKE '%.aspx')
AND (LeafName NOT LIKE '%.xfp')
AND (LeafName NOT LIKE '%.dwp')
AND (LeafName NOT LIKE '%template%')
AND (LeafName NOT LIKE '%.inf')
AND (LeafName NOT LIKE '%.css')

Reference: http://www.codeproject.com/kb/dotnet/QueriesToAnalyzeSPUsage.aspx and http://www.mcplusa.com/blog/2009/07/use-sql-queries-to-find-sharepoint-doc-count/