Usefull Sharepoint SQL Queries

A collegue from mine mentioned a link that leads to some SQL queries that you cannot (easily) have with a custom build webpart (with or without using webservices).. Some examples plz! Oh yeah please note that it's not really recommended to use SQL queries on a Sharepoint databases. I think a good guideline is to use it when there are (or few) users online so a deadlock can be avoided at all times.

List of unhosted pages in the SharePoint solution:

select Webs.FullUrl As SiteUrl, 
case when [dirname] = '' 
then '/'+[leafname] 
else '/'+[dirname]+'/'+[leafname] 
end as [Page Url], 
CAST((CAST(CAST(Size as decimal(10,2))/1024 As 
   decimal(10,2))/1024) AS Decimal(10,2))   AS  'File Size in MB'
FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
where [type]=0 
and [leafname] like ('%.aspx') 
and [dirname] not like ('%_catalogs/%') 
and [dirname] not like ('%/Forms') 
and [content] is not null 
and [dirname] not like ('%Lists/%') 
and [setuppath] is not null 
order by [Page Url];

List of top level and sub sites in the portal and the number of users:

select  webs.FullUrl ,Webs.Title, COUNT(WebMembers.UserId) As 'Total User'
from Webs INNER JOIN WebMembers 
ON Webs.Id = WebMembers.WebId
where fullurl  like '%sites%' AND fullUrl <> 'MySite' AND fullUrl <> 'personal'
Group BY webs.FullUrl,  Webs.Title
Order By  'Total User' desc

 

In addition to a comment from this post. If you want to check the usage of your portal and it's sites you can use this code :

 

try
{
//Get the sitecollection through the Administration model using SPGlobalAdmin and SPVirtualServer
SPGlobalAdmin globalAdmin = new SPGlobalAdmin();
System.Uri uri = new System.Uri("http://rldspst01");
SPVirtualServer virtualServer = globalAdmin.OpenVirtualServer(uri);
SPSiteCollection siteCollections = virtualServer.Sites;

//For each site in the sitecollection collect all relevant information from which the content or security is changed today
foreach (SPSite site in siteCollections)
{
output.Write("<table><tr>");
output.Write("<td>" +site.RootWeb.Title.ToString() + "</td>");
output.Write("<td>" +site.RootWeb.Url.ToString() + "</td>");
output.Write("<td>" +site.LastContentModifiedDate + "</td>");
output.Write("<td>" +site.LastSecurityModifiedDate + "</td>");
//We also want to publish information about the sizing of the site
SPSite.UsageInfo info = site.Usage;
output.Write("<td>" +Convert.ToString(info.Storage / 1024 / 1024)+ "</td>");

//Disposing of the objects
site.RootWeb.Close();
site.RootWeb.Dispose();
site.Close();
site.Dispose();
}
}
catch(Exception error)
{
Console.WriteLine(error.Message.ToString());
}

Please be aware that you can only execute this if you have the proper Sharepoint admin rights. And you got to set the trustlevel in the web.config from WSSMinimum to FULL