Brijesh's Blog

July 30, 2008

SQL queries for analyzing SharePoint farms

Filed under: Uncategorized — brij28 @ 7:04 pm

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

Use SP_DB01


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], 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 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.bwused, d.diskquota, d.lastcontentchange, a.TimeCreated

Order by a.fullurl

Note: Repeat above query for each SharePoint content database.



Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at

%d bloggers like this: