Today, we are going to see the query for returning Webs and Site Collections available in the Database (WebApplication).
–Returns Total Number of Site Collections in WebApplication
select count(*) as ‘Total Site Collection’ from sites
–Returns Root Site Title for each Site Collection available in WebApplication
select Title as ‘Root Web Title’, Sites.RootWebId, Sites.Id as ‘Site Collection ID’ from webs inner join Sites on Webs.Id = Sites.RootWebId
–Returns Total Web Sites in WebApplication
select count(*) from Webs
–Returns WebSite Title and Site Id
select Title as ‘Site title’,FullUrl, SiteId as ‘Site Collection Id’ from Webs order by SiteId
–Returns Total number of Web Sites under each SiteCollection
select SiteId, count(*) as ‘Total Sub Sites’ from Webs inner join Sites on Sites.Id = Webs.SiteId group by SiteId
Webs Database stores the informations on Web Sites.
Sites Database stores the informations on Site Collections.
March 20, 2009 at 4:13 AM
Great article, thanks for the information.
August 20, 2009 at 11:45 AM
Nice article. Does it make sense to use this access to tables in the SQL server bypassing SPQuery, and how to do this?
How to approach the internal SQLServer? Do you have a small example for this?
September 26, 2009 at 12:18 AM
Five reasons not to directly query SharePoint databases
http://www.sharepoint4arabs.com/AymanElHattab/Lists/Posts/Post.aspx?ID=99