Reorganizing or rebuilding indexes

Indexes are structures that can help speed up your queries. You can list all the indexes in your database tables and provide additional information such as the name, type, and fragmentation. To get all the indexes, you will have to get a handle to each table and access the Indexes property:

$table.Indexes

Each index, in turn, has its own methods and properties. Some properties that you may be interested in are Name, IndexType, Pages, FillFactor, PadIndex, and SpaceUsed. It also has a method EnumFragmentation(), which retrieves the current fragmentation value. Here is an example script to retrieve indexes and some properties, including fragmentation information:

Import-Module SQLPS -DisableNameChecking

#current server name
$servername = "ROGUE"

$server = New-Object "Microsoft.SqlServer.Management.Smo.Server" $servername
$dbname = "Chinook"

$result = @()

$db = $server.Databases[$dbname]
$db.Tables | 
ForEach-Object {
    $table = $_
    $table.Indexes...