Tuesday, March 24, 2009

Rebuild all indexes on all tables in a database using Powershell

For one or more reasons you may want to do database maintenance like index defrags or rebuilds using Powershell. Good news is that this is quite straightforward. If you instantiate the Microsoft.SqlServer.Management.Smo.Database class, you have access to a "tables" collection. There is a method that applies to the tables collection called RebuildIndexes. This method takes Fill Factor as an input parameter. So we have the tools to do what we need to, now its a case of writing the logic to rebuild the indexes on each table. With powershell thats a breeze, since we can run a foreach loop. So assuming that $db is our database objects with $db.tables being the tables collection, we could write:

foreach ($t in $db.tables) {$t.RebuildIndexes(90)}

Another Powershell one liner! This is the most simple form of rebuilding tables in a database using powershell. One could extend this to rebuild all tables in all user databases on a server, or on a list of servers. Or one could rebuild indexes on specific tables.

No comments:

Post a Comment