SQL queries for analyzing SharePoint farms

=====================================================
Query to get total number of items in each list of SharePoint farm:
=====================================================

Use SP_DB01

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

Note: Repeat above query for each SharePoint content database.

==========================
Query to find all site collections:
==========================

Use SP_DB01

Select SiteId, FullUrl, Title, Author, TimeCreated
From Webs
Where ParentWebId IS NULL

Note: Repeat above query for each SharePoint content database.

=======================================================================================
Query to get Site Collection Statistics - Created, Owner, Contents size, Bandwidth usage, Last Modified etc.
=======================================================================================

Use SP_DB01

select distinct a.fullurl as [SiteUrl], a.TimeCreated as Created,

b.tp_login as [SiteAdmin],

sum(cast(c.size as decimal))/1024/1024 as [recyclebin],

cast(d.bwused as decimal)/1024/1024 as [BandwidthUsed],

cast(d.diskused as decimal)/1024/1024 as [SiteSize],

cast(d.diskquota as decimal)/1024/1024 as [SiteMaxQuota],

d.id as [SiteID],(select db_name(dbid) from master..sysprocesses where spid=@@SPID) as [Content_DB],

(select @@servername) as [ServerName],

d.lastcontentchange as [LastContentChange],

(select datediff(day,d.lastcontentchange,current_timestamp)) as [DaysSinceLastChange]

from webs as a inner join

sites as d on a.siteid=d.id inner join

userinfo as b on a.siteid=b.tp_siteid left join

recyclebin as c on a.siteid=c.siteid where b.tp_siteadmin = '1' and a.parentwebid is null

group by a.fullurl, b.tp_login, d.diskused, d.id, d.bwused, d.diskquota, d.lastcontentchange, a.TimeCreated

Order by a.fullurl

Note: Repeat above query for each SharePoint content database.

References:
http://blogs.technet.com/corybu
http://www.codeplex.com/MSITSRF
http://www.codeproject.com/KB/dotnet/QueriesToAnalyzeSPUsage.aspx