How to rebuild or reorganize indexes in a specified SQL server database (PowerShell)

Introduction

This PowerShell script sample shows how to rebuild or reorganize indexes in a specified database.

Scenarios

This script will list all the indexes in a database. We can choose to rebuild or reorganize the indexes according to their defragmentation. If avg_fragmentation_in_percent_value is higher than 30 and page_cout is more than 1000, we can rebuild the indexes. If avg_fragmentation_in_percent_value is between 5 and 30 and page_count is more than 1000, we can reorganize the indexes.

Script

You can use this script in this way: 
1. Run Microsoft PowerShell as Administrator

2. Run the script in the form: &Path
For example: &"D:\Syncup\OneScript\Julie\RebuildOrReorganizeIndexes.ps1"
 

3. Press "Enter" and enter the server name and database name.
Note: Server name should be in the format: Server Name\ Instance Name. If it's a default instance, please enter the server name.

4. Press "Enter" and choose between Windows and SQL authentication. If you choose SQL Server authentication, please enter the correct user name and password.

5. When the script finishes running, we'll get the following figure:
 

Here are some code snippets for your reference.

Function GetChoice($message)
{
    $Win = New-Object System.Management.Automation.Host.ChoiceDescription "&Windows","Windows authentication"
    $SQL = New-Object System.Management.Automation.Host.ChoiceDescription "&SQL Server","SQL Server authentication"
    $Quit = New-Object System.Management.Automation.Host.ChoiceDescription "&Quit","Choose to quit"
    $choices = [System.Management.Automation.Host.ChoiceDescription[]]($Win,$SQL,$Quit)
    $caption = "Confirming"
    $result = $Host.UI.PromptForChoice($caption,$message,$choices,0)
    $result
}

Function GetChoice($message)
{
    $Win = New-Object System.Management.Automation.Host.ChoiceDescription "&Windows","Windows authentication"
    $SQL = New-Object System.Management.Automation.Host.ChoiceDescription "&SQL Server","SQL Server authentication"
    $Quit = New-Object System.Management.Automation.Host.ChoiceDescription "&Quit","Choose to quit"
    $choices = [System.Management.Automation.Host.ChoiceDescription[]]($Win,$SQL,$Quit)
    $caption = "Confirming"
    $result = $Host.UI.PromptForChoice($caption,$message,$choices,0)
    $result
}

Prerequisites

SQL Server 2005/SQL Server 2008/SQL Server 2008 R2/ SQL Server 2012

Additional Resources

Forum Threads:

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/558c2c58-0087-458d-9199-8b9f1708feec

Microsoft All-In-One Script Framework is an automation script sample library for IT Professionals. The key value that All-In-One Script Framework is trying to deliver is Scenario-Focused Script Samples driven by IT Pros' real-world pains and needs. The team is monitoring all TechNet forums, IT Pros' support calls to Microsoft, and script requests submitted to TechNet Script Repository. We collect frequently asked IT scenarios, and create script samples to automate the tasks and save some time for IT Pros. The team of All-In-One Script Framework sincerely hope that these customer-driven automation script samples can help our IT community in this script-centric move.