A couple years ago I wrote up a quick PowerShell script that would Truncate all Items in an SPList in SharePoint Using ProcessBatchData Command – which I then went on to post on the TechNet Gallery
And while this is a great way to quickly delete items in a SharePoint list, it always bothered me that it wasn’t a true truncate because SharePoint keeps track of the item IDs that have been used in the list and generates a fresh incremental ID number every time. Now, in a production system that’s fine, but when you’re working with automation and testing and doing constant delivery and integration of your applications, this continuous running of loads and truncates can often run up your ID and just from an aesthetic point of view these IDs can start to look pretty ugly. It also becomes a problem if you are implementing some sort of integration testing with these loads and you want to rely on certain rows with specific IDs to be in the list when you are running some of your unit testing.
So, for those of you who like to keep your list IDs clean or want to rely on certain IDs to be created during your SharePoint list loads, there is actually a pretty easy way to reset the ID value for a list.
First, you’re going to have to know the GUID of your SharePoint list. Easy to get by just going to the list settings page. Right click on “Title, description and navigation” and copy the URL. Paste that into notepad and copy everything after “List=” in the string. That’s your URL Encoded GUID for the list. All you need to do is decode it.
Of course, if we’re automating our environments and prep, we may not want to have to deal with UI since it’s a manual process, so it’s a good thing we have something like PowerShell to help us identify what the listGUID is for various SharePoint lists. You can use something like this to pull the listGUID of your choice:
Get-SPSite http://sharepoint | Get-SPWeb -Limit ALL | %{$_.Lists} |?{$_.Title –eq "listName"} |ft Title, ParentWebURL, ID
and of course, modify as needed to just return the ID into a $variable that you can use later in your scripts.
$listGUID = Get-SPSite http://sharepoint | Get-SPWeb -Limit ALL | %{$_.Lists} | Where-Object {($_.Title –eq "FAQ") -and ($_.ParentWebUrl -eq "/")} | select ID
And now you have the $listGUID.ID for using later on in prepping the SQL data in order to reset the list item IDs
UPDATE [WSS_Content].dbo.AllListsAux set NextAvailableId=1 where ListID='listGuid'