Get a count of ALL Lists and Document Libraries in your SharePoint Site

I needed to provide a report of all List and Document Libraries from my SharePoint Site/Child Sites and SharePoint Out of the Box does not provide this feature.

There are however, various options to getting this report, some which are listed below. The various options may give you site count from the top-level sites, but not child sites.

Options:

1.  Use SharePoint Designer reports:– Only gives top-level result

2.  Use SPSite  and SPWebCollection:– This involves writing code and iterating though your collection.

3.       Use Client Object Model as shown here on MSDN:- http://msdn.microsoft.com/en-us/library/ee538683%28v=office.14%29.aspx. Here again you have to iterate and get your data.

4.       My favorite is using good old SQL. This assumes that you have access to SQL Server management Studio and that you have the necessary credentials to your SharePoint SQL. Note that accessing your SQL directly is not recommended by Microsoft, but sometimes you have to do what you have to doJ So here is the SQL that will give you ALL List and Documents throughout your Site:

SELECT     AllLists.tp_Title, Webs.SiteId, Webs.FullUrl, AllLists.tp_RootFolder, Webs.ParentWebId

FROM         AllLists INNER JOIN

                      Webs ON AllLists.tp_WebId = Webs.Id

ORDER BY Webs.FullUrl

Feel free to refine it to your needs and share your successes.

I hope this helps someone out there.