The following is a PowerShell script that will delete SharePoint items in a specific list that match the passed-in query. I ended up writing this at work to delete a lot of items from a list (1 million, to be exact).

Code is after the jump.

The problem is that SharePoint does not expose this functionality anywhere. So I write the script to query for the items, delete all the returned items, query again (paging issues), delete again and keep going until no more items are found. For paging, make sure to get no more than about 2,000 items, otherwise queries aren’t performant and you may even get exceptions thrown.

# Delete an item from the announcements list by using a CAML query

param(
    [string] $web = $(throw "The parameter web is required"),
    [string] $list = $(throw "The parameter list is required"),
    [string] $caml = $(throw "The parameter caml is required"),</code>
    [int] $rowLimit = 1000,
    [switch] $whatif = $true
)

[System.reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
$spsite = New-Object -TypeName "Microsoft.SharePoint.SPSite" -ArgumentList $web
$spweb = $spsite.OpenWeb()
$splist = $spweb.Lists[$list]

$query=new-object -TypeName "Microsoft.SharePoint.SPQuery"
$query.Query = $caml
$query.RowLimit = $rowLimit

$col = $null
do
{
    $col=$splist.GetItems($query)

    Write-Host ("Items returned = [" + $col.Count + "]")

    $col | % {
        Write-Host ("Found item with ID = [" + $_.ID + "] and title = [" + $_.Title + "]")
        if (!$whatif)
        {
            Write-Host ("Deleting item with ID = " + $_.ID)
            $_.Delete()
        }
    }

    $query.ListItemCollectionPosition = $col.ListItemCollectionPosition
}
while ($col.ListItemCollectionPosition -ne $null)

$spweb.Dispose()
$spsite.Dispose()

Sample usage:

DeleteSPItemsWithCaml.ps1
 -web http://test06/sites/english
 -list "Pages"
 -whatIf:$true
 -caml "<Where><Contains><FieldRef Name=`"Title`" /><Value Type=`"Text`">Test</Value></Contains></Where>"

This will list out, but not delete, all the items in the Pages list on my test box/site where the Title field contains “Test”. Notice that quotes are escaped with the ` symbol.

DeleteSPItemsWithCaml.ps1
 -web http://test06/sites/english
 -list "Other Content"
 -whatIf:$false
 -rowLimit 10
 -caml "<Where><Contains><FieldRef Name=`"Title`" /><Value Type=`"Text`">Category1</Value></Contains></Where>"

This will delete all matching items. The difference is that now the whatIf parameter is false, so this is not a “test”. Also, I’ve changed the rowLimit parameter, which results in each query getting 10 items at a time.

I use the whatIf parameter in practically all my scripts, and set it to a default of true, so that I can test out the script without screwing anything up. In this case, it’s particularly important.

A final note on the script: when running it against our huge list of a million items, we noticed incredibly slow perf. One of my coworkers found a solution, though: sort the results so you’re deleting the largest items first. Here’s a sample query for that. The relevant CAML is highlighted.

  <Where>
    <Lt>
      <FieldRef Name="StartTime" />
      <Value Type="DateTime">2009-11-18T12:24:59Z</Value>
    </Lt>
  </Where>
  <OrderBy>
    <FieldRef Name="JobSize" Ascending="FALSE" />
  </OrderBy>
Advertisements