Tuesday, May 30, 2006

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 :


    //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("<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
    catch(Exception error)

    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


    Anonymous said...

    How can I get webpart usage report directly from query. I tried using SPCA ( sharepoint configuration analyzer tool) but due to some reason it got failed.

    Ayman M El-Hattab said...

    5 reasons not to query SharePoint Databases :

    Meinung said...

    Thank you,
    Good point !
    I will modify my post code sample to take this in account.

    Anonymous said...

    After each of the table names, you can add:

    with (nolock)

    and that will make sure your query doesn't block any other query.


    electronic signature said...

    Grateful to check out your website, I seem to be ahead to more excellent sites and I wish that you wrote more informative post for us. Well done work.