benc's blog

Here's a couple quick queries to try against a SharePoint content database. Nothing too interesting, but I've found them useful once or twice as a SharePoint administrator, so I thought I'd share them.

The usual caveats about running queries directly apply, of course. Microsoft's Keith Richie puts it best in his latest post about finding orphan sites (a good read, by the way):

DISCLAIMER: This post shows using Query Analyzer to query data in your SharePoint sites. By no means does this mean that you should change any thing in the database. This is simply for "READING" values. And even this should be done during Off-Peak hours.

This first query finds the 100 largest documents across all sites in the current content database. Can be useful for identifying bottlenecks.

  SELECT TOP 100      DirName + '/' + LeafName AS name,      DATALENGTH(Content) AS contentlen  FROM      Docs  --WHERE  --    (LeafName LIKE '%.doc')    -- optionally, find documents of this type  ORDER BY      contentlen DESC  

Second, here's a query to find email addresses by pattern. Keep in mind that data mining for email addresses is kinda evil… just make sure you're doing this for a good cause.

One such cause: A number of your users have alphanumeric pagers with their own email addresses (e.g., 5558675309@pager.phonecompany.com). These email addresses are set up to receive certain alerts from certain lists (e.g., custom lists named "High-Priority Service Requests" and the like.) Your company needs to know what those numbers are. And you'd rather not dig through multiple pages of user info on multiple sites, searching for email addresses not matching the pattern "@example.com". The solution:

  SELECT      UserInfo.tp_Email,      UserInfo.tp_Login,      UserInfo.tp_Title,      UserInfo.tp_Notes,      UserInfo.tp_Deleted,      UserInfo.tp_SiteAdmin,      Sites.FullUrl  FROM      Sites,      UserInfo  WHERE      Sites.Id = UserInfo.tp_SiteId      AND ASCII(UserInfo.tp_Email) > 0      AND UserInfo.tp_Email NOT LIKE '%@example.com'  ORDER BY      UserInfo.tp_Email

Finally: I've got a really neat (and unambiguously useful, for once) query coming up later, so stay tuned.

–>

Filed under: Uncategorized

Here's a couple quick queries to try against a SharePoint content database. Nothing too interesting, but I've found them useful once or twice as a SharePoint administrator, so I thought I'd share them.

The usual caveats about running queries directly apply, of course. Microsoft's Keith Richie puts it best in his latest post about finding orphan sites (a good read, by the way):

DISCLAIMER: This post shows using Query Analyzer to query data in your SharePoint sites. By no means does this mean that you should change any thing in the database. This is simply for "READING" values. And even this should be done during Off-Peak hours.

This first query finds the 100 largest documents across all sites in the current content database. Can be useful for identifying bottlenecks.

  SELECT TOP 100      DirName + '/' + LeafName AS name,      DATALENGTH(Content) AS contentlen  FROM      Docs  --WHERE  --    (LeafName LIKE '%.doc')    -- optionally, find documents of this type  ORDER BY      contentlen DESC  

Second, here's a query to find email addresses by pattern. Keep in mind that data mining for email addresses is kinda evil… just make sure you're doing this for a good cause.

One such cause: A number of your users have alphanumeric pagers with their own email addresses (e.g., 5558675309@pager.phonecompany.com). These email addresses are set up to receive certain alerts from certain lists (e.g., custom lists named "High-Priority Service Requests" and the like.) Your company needs to know what those numbers are. And you'd rather not dig through multiple pages of user info on multiple sites, searching for email addresses not matching the pattern "@example.com". The solution:

  SELECT      UserInfo.tp_Email,      UserInfo.tp_Login,      UserInfo.tp_Title,      UserInfo.tp_Notes,      UserInfo.tp_Deleted,      UserInfo.tp_SiteAdmin,      Sites.FullUrl  FROM      Sites,      UserInfo  WHERE      Sites.Id = UserInfo.tp_SiteId      AND ASCII(UserInfo.tp_Email) > 0      AND UserInfo.tp_Email NOT LIKE '%@example.com'  ORDER BY      UserInfo.tp_Email

Finally: I've got a really neat (and unambiguously useful, for once) query coming up later, so stay tuned.

–>

Filed under: Uncategorized