Book Image

PowerShell for SQL Server Essentials

By : Donabel Santos
Book Image

PowerShell for SQL Server Essentials

By: Donabel Santos

Overview of this book

Table of Contents (15 chapters)
PowerShell for SQL Server Essentials
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Implementing Reusability with Functions and Modules
Index

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...