What is the best way to enumerate all SPWebs in an SPSite and also to enumerate folders within document libraries within each of the SPWebs? (essentially I want to find out the urls of all SPWebs, and the number of folders and amount of data being stored in each).
I have achieved the above in C# using the SharePoint API but that takes a long time to run. It seems like the sort of thing that would be perfectly suited to an SQL query, but I understand that that wouldn't be recommended, but surely?! Does anyone have any SQL to achieve the above?
Use of SQL queries, even for read-only is unsupported. For the read-only case this is because of locking. You could use NOLOCK but then your results aren't necessarily correct anyway so there's not much point.
I would schedule running the program you've already written overnight to generate a report for the following morning. Unfortunately there's really no better way to do it. You may like to make it a timer job.
You may also want to look at a CodePlex tool called SharePoint Usage Monitor and/or products from major vendors that provide similar information. They all work the same way, to retrieve this data out-of-hours.