Using SPSiteDataQuery with Powershell

I admit it – I didn’t realize that you could use SPSiteDataQuery in SharePoint 2007. I totally thought it was specific to 2010. I tried using it in powershell so that I could get specific lists/libraries within a site collection. Here’s what the general code looks like:

$site = new-object Microsoft.SharePoint.SPSite(“<url>”)

$query = new-object Microsoft.SharePoint.SPSiteDataQuery

$query.ViewFields = “<FieldRef Name=’Title’ />”

$query.Webs = “<Webs Scope=’SiteCollection’ />”

$query.Lists = “<Lists ServerTemplate=’101′ Hidden=’false’ />”

$results = new-object System.Data.DataTable

$results = $site.rootweb.GetSiteData($query)


foreach($row in $results.rows)


Write-host $row.Title


I learned some things along the way. Like SPSiteDataQuery is kind of limiting.  Below are some lessons I learned:

1. So far I know the result gets ListID, WebID, and SPListItem ID returned by default.

2. A lowercase or spelling mistake either returns an error or no results – it took me awhile to realize that.

3. Search results can be incomplete when you use a CAML query that uses the SPSiteDataQuery class to search content on a SharePoint server 2007 or Windows SharePoint Services 3.0 site. According to the KB, the problem occurs when:

    • The CAML query searches for content in more than 10 document libraries or document lists.
    • The CAML query searches for content across more than 10 explicit GUIDs.

The following condition must also be true:

    • The column mapping for a field in the Where clause is not the same in all the document libraries, in all the document lists, or in all the GUIDs in the query.

4. There wasn’t an easy way to only get items that were created by users (I wanted to exclude items that were in system created documents libraries like “Style Library”). The only thing I could find was getting the author field and excluding items where author is equal to Sharepoint\system.

5. You can only specify one list type at a time (for example, I can’t get back all custom lists and document libraries within one query).

6. You can only include/exclude specific lists by hardcoding the list id

MSDN: SPSiteDataQuery class


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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: