Rebuilding T-SQL Indexes From Powershell

Here is a Windows Powershell function I wrote to call a generic stored procedure that will rebuild all indexes in a database.  (I found the stored procedure here: http://www.wisesoft.co.uk/scripts/t-sql_defrag_indexes_for_database.aspx) This turns out to be extremely useful for rebuilding indexes on a SQL Server-based connected directory at the end of each run of a Microsoft Forefront Identity Manager (FIM) MA that has undergone a lot of changes.

PowerShell function:  

Function Run-RebuildIndexes
{
      param([string]$DBName,[string]$SQLServerName=“localhost”,[string]$SQLServerPort=“1433”)

      $log.debug(“Run-RebuildIndexes for MA=”+$ma.MAName +” DBName=”+$DBName)
      $SqlConnection=New-ObjectSystem.Data.SqlClient.SqlConnection;
      $SqlConnection.ConnectionString =“Data Source=”+$SQLServerName+“;Initial Catalog=”+$DBName+“;Integrated Security=True”;
      $SqlCommand=New-ObjectSystem.Data.SqlClient.SqlCommand;
      $SqlCommand.CommandText =“DECLARE  @return_value int                                          EXEC  @return_value = [dbo].[USP_REBUILD_ALL_IDX]                                          SELECT      ‘Success’ = @return_value”;
      $SqlCommand.Connection =$SqlConnection;
      $SqlCommand.CommandType = [System.Data.CommandType]‘Text’;
      $SqlConnection.Open();
      $SqlAdapter=New-ObjectSystem.Data.SqlClient.SqlDataAdapter;
      $SqlAdapter.SelectCommand =$SqlCommand;
      $DataSet=New-ObjectSystem.Data.DataSet
      $nRecs=$SqlAdapter.Fill($DataSet)
      $SqlConnection.Close();
      $results=New-ObjectSystem.Collections.ArrayList;
      if ( $nRecs-gt 0 )
      {
            $ActiveDataSet=$DataSet.tables[0]
            foreach($recin$ActiveDataSet)
            {
                  $success=$rec.Success;
                  $log.Debug(“Index rebuild result code: “+$rec.Success);
                  if($success-ne“0”)
                  {
                        return$false;
                  }
                  else
                  {
                        return$true;
                  }
            }
      }
      else
      {
            return$false;
      }
}
T-SQL stored procedure:
GO
USE [DBName]
GO
 
/****** Object:  StoredProcedure [dbo].[USP_REBUILD_ALL_IDX]    Script Date: 01/05/2012 14:14:21 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
— =============================================
— Author:        <Nicholas Roy>
— Create date: <Jan 5, 2012>
— Description:   <Rebuild all indexes in database>
— =============================================
CREATE PROCEDURE [dbo].[USP_REBUILD_ALL_IDX]
 
AS
BEGIN
      — SET NOCOUNT ON added to prevent extra result sets from
      — interfering with SELECT statements.
      SET NOCOUNT ON;
 
      DECLARE @SQL NVARCHAR(MAX)
      SELECT @SQL =(
      SELECT N’ALTER INDEX ALL ON ‘ + QUOTENAME(s.name) + ‘.’ + QUOTENAME(t.name) + N’ REBUILD
      ‘
      FROM sys.tables t
      JOIN sys.schemas s on t.schema_id = s.schema_id
      FOR XML PATH(),TYPE).value(‘.’,‘NVARCHAR(MAX)’)
 
      exec sp_executesql@SQL
END
 
GO

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.