Rebuild all indexes on all tables in the SQL Server database

One of the key tasks of a DBA is to maintain the database indexes and make sure they are not fragmented. You can use a sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes based on fragmentation. However, sometimes we may need to rebuild all indexes on all tables in the database, especially if you have to change any index property such as fill factor, compression, etc.

For this task, I wrote a following script that accepts parameters to change the properties of the indexes, and dynamically generate and execute ALTER INDEX statements. This script is compatible with SQL Server 2005 and above versions.

Here is this script:

--/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
--Arguments				Data Type				Description
--------------			------------			------------
--@FillFactor			[int]					Specifies a percentage that indicates how full the Database Engine should make the leaf level
--												of each index page during index creation or alteration. The valid inputs for this parameter
--												must be an integer value from 1 to 100 The default is 0.
--												For more information, see http://technet.microsoft.com/en-us/library/ms177459.aspx.

--@PadIndex				[varchar](3)			Specifies index padding. The PAD_INDEX option is useful only when FILLFACTOR is specified,
--												because PAD_INDEX uses the percentage specified by FILLFACTOR. If the percentage specified
--												for FILLFACTOR is not large enough to allow for one row, the Database Engine internally
--												overrides the percentage to allow for the minimum. The number of rows on an intermediate
--												index page is never less than two, regardless of how low the value of fillfactor. The valid
--												inputs for this parameter are ON or OFF. The default is OFF.
--												For more information, see http://technet.microsoft.com/en-us/library/ms188783.aspx.

--@SortInTempDB			[varchar](3)			Specifies whether to store temporary sort results in tempdb. The valid inputs for this
--												parameter are ON or OFF. The default is OFF.
--												For more information, see http://technet.microsoft.com/en-us/library/ms188281.aspx.

--@OnlineRebuild		[varchar](3)			Specifies whether underlying tables and associated indexes are available for queries and data
--												modification during the index operation. The valid inputs for this parameter are ON or OFF.
--												The default is OFF.
--												Note: Online index operations are only available in Enterprise edition of Microsoft
--														SQL Server 2005 and above.
--												For more information, see http://technet.microsoft.com/en-us/library/ms191261.aspx.

--@DataCompression		[varchar](4)			Specifies the data compression option for the specified index, partition number, or range of
--												partitions. The options  for this parameter are as follows:
--													> NONE - Index or specified partitions are not compressed.
--													> ROW  - Index or specified partitions are compressed by using row compression.
--													> PAGE - Index or specified partitions are compressed by using page compression.
--												The default is NONE.
--												Note: Data compression feature is only available in Enterprise edition of Microsoft
--														SQL Server 2005 and above.
--												For more information about compression, see http://technet.microsoft.com/en-us/library/cc280449.aspx.

--@MaxDOP				[int]					Overrides the max degree of parallelism configuration option for the duration of the index
--												operation. The valid input for this parameter can be between 0 and 64, but should not exceed
--												number of processors available to SQL Server.
--												For more information, see http://technet.microsoft.com/en-us/library/ms189094.aspx.
--- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*/

-- Ensure a USE <databasename> statement has been executed first.

SET NOCOUNT ON;

DECLARE  @Version							[numeric] (18, 10)
		,@SQLStatementID					[int]
		,@CurrentTSQLToExecute				[nvarchar](max)
		,@FillFactor						[int]		 = 100 -- Change if needed
		,@PadIndex							[varchar](3) = N'OFF' -- Change if needed
		,@SortInTempDB						[varchar](3) = N'OFF' -- Change if needed
		,@OnlineRebuild						[varchar](3) = N'OFF' -- Change if needed
		,@LOBCompaction						[varchar](3) = N'ON' -- Change if needed
		,@DataCompression					[varchar](4) = N'NONE' -- Change if needed
		,@MaxDOP							[int]		 = NULL -- Change if needed
		,@IncludeDataCompressionArgument	[char](1);

IF OBJECT_ID(N'TempDb.dbo.#Work_To_Do') IS NOT NULL
    DROP TABLE #Work_To_Do
CREATE TABLE #Work_To_Do
    (
      [sql_id] [int] IDENTITY(1, 1)
                     PRIMARY KEY ,
      [tsql_text] [varchar](1024) ,
      [completed] [bit]
    )

SET @Version = CAST(LEFT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)), CHARINDEX('.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - 1) + N'.' + REPLACE(RIGHT(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)), LEN(CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128))) - CHARINDEX('.', CAST(SERVERPROPERTY(N'ProductVersion') AS [nvarchar](128)))), N'.', N'') AS [numeric](18, 10))

IF @DataCompression IN (N'PAGE', N'ROW', N'NONE')
	AND (
		@Version >= 10.0
		AND SERVERPROPERTY(N'EngineEdition') = 3
		)
BEGIN
	SET @IncludeDataCompressionArgument = N'Y'
END

IF @IncludeDataCompressionArgument IS NULL
BEGIN
	SET @IncludeDataCompressionArgument = N'N'
END

INSERT INTO #Work_To_Do ([tsql_text], [completed])
SELECT 'ALTER INDEX [' + i.[name] + '] ON' + SPACE(1) + QUOTENAME(t2.[TABLE_CATALOG]) + '.' + QUOTENAME(t2.[TABLE_SCHEMA]) + '.' + QUOTENAME(t2.[TABLE_NAME]) + SPACE(1) + 'REBUILD WITH (' + SPACE(1) + + CASE
		WHEN @PadIndex IS NULL
			THEN 'PAD_INDEX =' + SPACE(1) + CASE i.[is_padded]
					WHEN 1
						THEN 'ON'
					WHEN 0
						THEN 'OFF'
					END
		ELSE 'PAD_INDEX =' + SPACE(1) + @PadIndex
		END + CASE
		WHEN @FillFactor IS NULL
			THEN ', FILLFACTOR =' + SPACE(1) + CONVERT([varchar](3), REPLACE(i.[fill_factor], 0, 100))
		ELSE ', FILLFACTOR =' + SPACE(1) + CONVERT([varchar](3), @FillFactor)
		END + CASE
		WHEN @SortInTempDB IS NULL
			THEN ''
		ELSE ', SORT_IN_TEMPDB =' + SPACE(1) + @SortInTempDB
		END + CASE
		WHEN @OnlineRebuild IS NULL
			THEN ''
		ELSE ', ONLINE =' + SPACE(1) + @OnlineRebuild
		END + ', STATISTICS_NORECOMPUTE =' + SPACE(1) + CASE st.[no_recompute]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END + ', ALLOW_ROW_LOCKS =' + SPACE(1) + CASE i.[allow_row_locks]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END + ', ALLOW_PAGE_LOCKS =' + SPACE(1) + CASE i.[allow_page_locks]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END + CASE
		WHEN @IncludeDataCompressionArgument = N'Y'
			THEN CASE
					WHEN @DataCompression IS NULL
						THEN ''
					ELSE ', DATA_COMPRESSION =' + SPACE(1) + @DataCompression
					END
		ELSE ''
		END + CASE
		WHEN @MaxDop IS NULL
			THEN ''
		ELSE ', MAXDOP =' + SPACE(1) + CONVERT([varchar](2), @MaxDOP)
		END + SPACE(1) + ')'
	,0
FROM [sys].[tables] t1
INNER JOIN [sys].[indexes] i ON t1.[object_id] = i.[object_id]
	AND i.[index_id] > 0
	AND i.[type] IN (1, 2)
INNER JOIN [INFORMATION_SCHEMA].[TABLES] t2 ON t1.[name] = t2.[TABLE_NAME]
	AND t2.[TABLE_TYPE] = 'BASE TABLE'
INNER JOIN [sys].[stats] AS st WITH (NOLOCK) ON st.[object_id] = t1.[object_id]
	AND st.[name] = i.[name]

SELECT @SQLStatementID = MIN([sql_id])
FROM #Work_To_Do
WHERE [completed] = 0

WHILE @SQLStatementID IS NOT NULL
BEGIN
	SELECT @CurrentTSQLToExecute = [tsql_text]
	FROM #Work_To_Do
	WHERE [sql_id] = @SQLStatementID

	PRINT @CurrentTSQLToExecute

	EXEC [sys].[sp_executesql] @CurrentTSQLToExecute

	UPDATE #Work_To_Do
	SET [completed] = 1
	WHERE [sql_id] = @SQLStatementID

	SELECT @SQLStatementID = MIN([sql_id])
	FROM #Work_To_Do
	WHERE [completed] = 0
END
Advertisements

SQL Server index related dynamic management views and functions (Part 2)

SQL Server uses indexes to sort and organize table data. It creates indexes based on ordering values from one or more selected columns. SQL Server automatically creates indexes when you define a primary key or unique constraint. You can use indexes to manage and track rows in a table or an indexed view. Indexes in general improve the performance on most data scans. However, when tables have records inserted or updated, the indexes become fragmented, which can adversely affect the query and response time. This is because, when we access data through an index, SQL Server read each index page in the specified range to retrieve the indexed values, and when highly fragmented, SQL Server database engine has to search many more pages, more levels, to retrieve the required data. This results in poor performance and causes your application to respond slowly.

That’s why, as a database administrator, it is one of our responsibilities to create efficient re-indexing strategy for our databases that will ensure that the database works fine and performs well. Although, SQL Server offers maintenance plans where you can add re-indexing task to re-index all indexes of all databases hosted on the SQL Server instance. The problem with this re-indexing task is that it is not suitable for very large databases (VLDBs) because it re-indexes all indexes of database, regardless of whether they are fragmented or not. Re-building all indexes can cause the following two issues:

  • When you re-build an index, it clears the stats for that index from SQL Server cache. This means you will loose the statistics information for indexes that are not fragmented.
  • Index maintenance can take longer to re-index the database indexes because you are effectively re-building indexes that do not need re-building.

Therefore, due to these problems, we need to create our own indexing strategy outside of the maintenance plans.

Checkout the part-2 of my three part article series on index-related dynamic management views and function here, in which I discussed about following index-related dynamic management function (DMF): sys.dm_db_index_physical_stats, which is useful to detect fragmentation in specific index, all indexes in a table or indexed view, or all indexes in databases, or all indexes in all databases. In this article, I also shared a stored procedure sp_rebuildindex that can be used to rebuild indexes based on fragmentation level.

This article is published on SSWUG.org.

Rebuild indexes based on fragmentation – sp_RebuildIndex

In my previous post here, I’ve discussed how we can detect fragmentation in SQL Server databases indexes using dynamic management view function sys.dm_db_index_physical_stats. In this post, I’m sharing my stored procedure which I’ve created a while ago to rebuild fragmented indexes based on the fragmentation level. This stored procedure is based on sys.dm_db_index_physical_stats and resides in master database. This procedure automatically rebuilds indexes if the fragmentation level is above 30% and reorganises indexes if fragmentation is less than 30%.

Code for the procedure is as follow:

USE [master]
GO

SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[sp_RebuildIndex] @FragmentationLevel [int] = 30
	,@SortInTempDB [varchar] (8) = 'OFF'
	,@Online [varchar] (8) = 'OFF'
	,@LOBCompaction [varchar] (8) = 'ON'
	,@ScanMode [varchar] (16) = 'DETAILED'
	,@MinRowsInTable [int] = 1000
	--Stored Procedure: [dbo].[sp_RebuildIndex]
	--Author: Basit A. Farooq
	--Version #: v1.0.0
	--********************
	-- Purpose/Comments
	--********************
	--Stored procedure to rebuild the indexes of database.
	--Indexes will be rebuild based on fragmentation criteria and scan mode.
AS
BEGIN
	SET NOCOUNT ON;

	--***********
	--Parameters
	--***********
	--@FragmentationLevel [int]
	--Specifies index fragementation level search criteria.
	--@SortInTempDB [varchar](8)
	--Specifies whether to store the sort results in tempdb. The default is OFF.
	--@LOBCompaction [varchar](8)
	--Specifies that all pages that contain large object (LOB) data are compacted.
	--The default is ON
	--@Online [varchar](8)
	--Specifies whether underlying tables and associated indexes are available for queries and
	--data modification during the index operation. The default is OFF.
	--@ScanMode [varchar](16) Is the name of the mode.
	--@ScanMode specifies the scan level that is used to obtain statistics.
	--Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED or DETAILED.
	--The default (NULL) is LIMITED.
	--The DETAILED mode scans all pages and returns all statistics.
	--The SAMPLED mode returns statistics based on a 1 percent sample of all the pages
	--in the index or heap. The modes are progressivelyslower from LIMITED to DETAILED,
	--because more work is performed in each mode.
	--@MinRowsInTable [int]
	--Specifies minimum rows in table for search criteria.
	--********
	--Usage
	--********
	--EXEC [<Database_Name>].[dbo].[sp_RebuildIndex]
	--EXEC [<Database_Name>].[dbo].[sp_RebuildIndex] 30, 'OFF', 'OFF', 'ON', 'LIMITED', 10
	DECLARE @StatementID [int]
		,@SQLStatement [varchar] (max)
		,@ErrorText [varchar] (256)
		,@SQLProcedureName [varchar] (64)

	SET @SQLProcedureName = 'sRebuildIndex'

	BEGIN TRY
		--Validating store procedure parameters
		SET @ErrorText = 'Invalid argument was supplied for @FragementationLevel parameter.' + ' Valid inputs for this parameter should be in the between 1 and 100'

		IF @FragmentationLevel NOT BETWEEN 1
				AND 100
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@FragmentationLevel
					)
		END

		SET @ErrorText = 'Invalid argument was supplied for @SortInTempDB parameter.' + ' Valid inputs for this parameter are ON or OFF.'

		IF @SortInTempDB != 'ON'
			AND @SortInTempDB != 'OFF'
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@SortInTempDB
					)
		END

		SET @ErrorText = 'Invalid argument was supplied for @LOBCompaction parameter.' + ' Valid inputs for this parameter are ON or OFF.'

		IF @LOBCompaction != 'ON'
			AND @LOBCompaction != 'OFF'
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@LOBCompaction
					)
		END

		SET @ErrorText = 'Invalid argument was supplied for @Online parameter.' + ' Valid inputs for this parameter are ON or OFF.'

		IF @Online != 'ON'
			AND @Online != 'OFF'
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@Online
					)
		END

		SET @ErrorText = 'Invalid argument was supplied for @ScanMode parameter.' + ' Valid inputs for this parameter are DEFAULT, NULL, LIMITED, SAMPLED or DETAILED.'

		IF @ScanMode != 'LIMITED'
			AND @ScanMode != 'NULL'
			AND @ScanMode != 'SAMPLED'
			AND @ScanMode != 'DETAILED'
			AND @ScanMode != 'DEFAULT'
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@ScanMode
					)
		END

		SET @ErrorText = 'Invalid argument was supplied for @MinRowsInTable parameter.' + ' Valid inputs for this parameter should be in the between 1 and 1000000000000000'

		IF @MinRowsInTable NOT BETWEEN 1
				AND 1000000000000000
		BEGIN
			RAISERROR (
					@ErrorText
					,11
					,1
					,@MinRowsInTable
					)
		END

		--Creating temporary tables.
		IF OBJECT_ID('Tempdb.dbo.#SQLStatementsStore') IS NOT NULL
			DROP TABLE #SQLStatementsStore

		CREATE TABLE #SQLStatementsStore (
			[SQLStatementID] [int] IDENTITY(1, 1)
			,[SQLstatement] [varchar](1024)
			)

		IF OBJECT_ID('Tempdb.dbo.#PartitionTables ') IS NOT NULL
			DROP TABLE #PartitionTables

		CREATE TABLE #PartitionTables (
			[ObjectId] [int]
			,[SchemaId] [int]
			,[TableName] [varchar](512)
			)

		IF OBJECT_ID('Tempdb.dbo.#Tables') IS NOT NULL
			DROP TABLE #Tables

		CREATE TABLE #Tables (
			[ObjectId] [int]
			,[SchemaId] [int]
			,[TableName] [varchar](512)
			)

		IF OBJECT_ID('Tempdb.dbo.#Index_Physical_Stats') IS NOT NULL
			DROP TABLE #Index_Physical_Stats

		CREATE TABLE #Index_Physical_Stats (
			[ObjectId] [int] NOT NULL
			,[TableName] [varchar](100) NOT NULL
			,[IndexId] [int] NOT NULL
			,[IndexName] [varchar](500) NOT NULL
			,[IndexPartitionNumber] [int] NOT NULL
			,[IndexType] [varchar](500) NOT NULL
			,[PadIndex] [varchar](256) NOT NULL
			,[IndexFillFactor] [int] NOT NULL
			,[StatisticsNoRecompute] [varchar](256) NOT NULL
			,[IgnoreDuplicateKey] [varchar](256) NOT NULL
			,[AllowRowLocks] [varchar](256) NOT NULL
			,[AllowPageLocks] [varchar](256) NOT NULL
			,[AvgFragmentationInPercent] [float] NOT NULL
			,[CountRows] [int] NOT NULL
			)

		INSERT INTO #PartitionTables (
			[ObjectId]
			,[SchemaId]
			,[TableName]
			)
		SELECT DISTINCT t.[object_id]
			,t.[schema_id]
			,t.[name]
		FROM [sys].[partitions] p
		INNER JOIN [sys].[tables] t ON t.[object_id] = p.[object_id]
			AND p.[partition_number] > 1

		INSERT INTO #Tables (
			[ObjectId]
			,[SchemaId]
			,[TableName]
			)
		SELECT t.[object_id]
			,t.[schema_id]
			,t.[name]
		FROM [sys].[tables] t
		WHERE t.[name] COLLATE Latin1_General_CI_AS NOT IN (
				SELECT [TableName]
				FROM #PartitionTables
				)

		INSERT INTO #Index_Physical_Stats (
			[ObjectId]
			,[TableName]
			,[IndexId]
			,[IndexName]
			,[IndexPartitionNumber]
			,[IndexType]
			,[PadIndex]
			,[IndexFillFactor]
			,[StatisticsNoRecompute]
			,[IgnoreDuplicateKey]
			,[AllowRowLocks]
			,[AllowPageLocks]
			,[AvgFragmentationInPercent]
			,[CountRows]
			)
		SELECT dm.[object_id]
			,DB_NAME(DB_ID()) + '.' + s.[name] + '.' + o.[name]
			,dm.[index_id]
			,i.[name]
			,dm.[partition_number]
			,dm.[index_type_desc]
			,[pad_index] = CASE i.[is_padded]
				WHEN 0
					THEN 'OFF'
				WHEN 1
					THEN 'ON'
				END
			,i.[fill_factor]
			,[statistics_norecompute] = CASE st.[no_recompute]
				WHEN 0
					THEN 'OFF'
				WHEN 1
					THEN 'ON'
				END
			,[ignore_dup_key] = CASE i.[ignore_dup_key]
				WHEN 0
					THEN 'OFF'
				WHEN 1
					THEN 'ON'
				END
			,[allow_row_locks] = CASE i.[allow_row_locks]
				WHEN 0
					THEN 'OFF'
				WHEN 1
					THEN 'ON'
				END
			,[allow_page_locks] = CASE i.[allow_page_locks]
				WHEN 0
					THEN 'OFF'
				WHEN 1
					THEN 'ON'
				END
			,dm.[avg_fragmentation_in_percent]
			,p.[rows]
		FROM [sys].[objects] o
		INNER JOIN [sys].[indexes] i ON o.[object_id] = i.[object_id]
			AND i.NAME <> 'NULL'
		INNER JOIN [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, @ScanMode) dm ON i.[object_id] = dm.[object_id]
			AND i.[index_id] = dm.[index_id]
			AND dm.[avg_fragmentation_in_percent] >= 5
		INNER JOIN [sys].[schemas] s ON o.[schema_id] = s.[schema_id]
		INNER JOIN [sys].[stats] st ON i.[name] COLLATE Latin1_General_CI_AS = st.[name] COLLATE Latin1_General_CI_AS
			AND o.[object_id] = st.[object_id]
			AND o.[type] COLLATE Latin1_General_CI_AS = 'U'
		INNER JOIN [sys].[partitions] p ON i.[index_id] = p.[index_id]
			AND o.[object_id] = p.[object_id]
			AND p.[rows] > @MinRowsInTable

		--Changing the fillfactor to 100 where fillfactor is 0
		UPDATE #Index_Physical_Stats
		SET IndexFillFactor = 100
		WHERE IndexFillFactor = 0

		-- Building quries to re-build partiton tables indexes partitions with
		-- fragmentation over 30 percent...
		INSERT INTO #SQLStatementsStore (SQLstatement)
		SELECT 'ALTER INDEX [' + i.[name] + '] ON ' + DB_NAME(DB_ID()) + '.' + s.[name] + '.' + pt.[TableName] + ' ' + 'REBUILD PARTITION = ' + CONVERT([varchar](10), ips.[IndexPartitionNumber])
		FROM #PartitionTables pt
		INNER JOIN [sys].[indexes] i ON pt.[ObjectId] = i.[object_id]
			AND i.NAME COLLATE Latin1_General_CI_AS <> 'NULL'
		INNER JOIN #Index_Physical_Stats ips ON pt.[ObjectId] = ips.[ObjectId]
			AND i.[index_id] = ips.[IndexId]
			AND ips.[AvgFragmentationInPercent] > @FragmentationLevel
		INNER JOIN [sys].[schemas] s ON pt.[SchemaId] = s.[schema_id]

		-- Building quries to re-build non-partiton tables indexes with
		-- fragmentation over 30 percent...
		INSERT INTO #SQLStatementsStore (SQLstatement)
		SELECT 'ALTER INDEX [' + i.[name] + '] ON ' + DB_NAME(DB_ID()) + '.' + s.[name] + '.' + t.[TableName] + ' ' + 'REBUILD WITH (' + ' PAD_INDEX = ' + CONVERT([varchar](8), ips.[PadIndex]) + ', FILLFACTOR = ' + CONVERT([varchar](8), ips.[IndexFillFactor]) + ', SORT_IN_TEMPDB = ' + CONVERT([varchar](8), @SortInTempDB) + ', STATISTICS_NORECOMPUTE = ' + CONVERT([varchar](8), ips.[StatisticsNoRecompute]) + ', ONLINE = ' + CONVERT([varchar](8), @Online) + ', ALLOW_ROW_LOCKS = ' + CONVERT([varchar](8), ips.[AllowRowLocks]) + ', ALLOW_PAGE_LOCKS = ' + CONVERT([varchar](8), ips.[AllowPageLocks]) + ' )'
		FROM #Tables t
		INNER JOIN [sys].[indexes] i ON t.[ObjectId] = i.[object_id]
			AND i.NAME COLLATE Latin1_General_CI_AS <> 'NULL'
		INNER JOIN #Index_Physical_Stats ips ON t.[ObjectId] = ips.[ObjectId]
			AND i.[index_id] = ips.[IndexId]
			AND ips.[AvgFragmentationInPercent] > @FragmentationLevel
		INNER JOIN [sys].[schemas] s ON t.[SchemaId] = s.[schema_id]

		-- Building quries to re-organise partiton tables indexes partitions with
		-- fragmentation between 5 and 30 percent...
		INSERT INTO #SQLStatementsStore (SQLstatement)
		SELECT 'ALTER INDEX [' + i.[name] + '] ON ' + DB_NAME(DB_ID()) + '.' + s.[name] + '.' + pt.[TableName] + ' ' + 'REORGANIZE PARTITION = ' + CONVERT([varchar](8), ips.[IndexPartitionNumber])
		FROM #PartitionTables pt
		INNER JOIN [sys].[indexes] i ON pt.[ObjectId] = i.[object_id]
			AND i.NAME COLLATE Latin1_General_CI_AS <> 'NULL'
		INNER JOIN #Index_Physical_Stats ips ON pt.[ObjectId] = ips.[ObjectId]
			AND i.[index_id] = ips.[IndexId]
			AND ips.[AvgFragmentationInPercent] BETWEEN 5
				AND @FragmentationLevel
		INNER JOIN [sys].[schemas] s ON pt.[SchemaId] = s.[schema_id]

		-- Building quries to re-build non-partiton tables indexes with
		-- fragmentation between 5 and 30 percent...
		INSERT INTO #SQLStatementsStore (SQLstatement)
		SELECT 'ALTER INDEX [' + i.[name] + '] ON ' + DB_NAME(DB_ID()) + '.' + s.[name] + '.' + t.[TableName] + ' ' + 'REORGANIZE WITH ( LOB_COMPACTION = ' + @LOBCompaction + ' )' AS 'query'
		FROM #Tables t
		INNER JOIN [sys].[indexes] i ON t.[ObjectId] = i.[object_id]
			AND i.NAME COLLATE Latin1_General_CI_AS <> 'NULL'
		INNER JOIN #Index_Physical_Stats ips ON t.[ObjectId] = ips.[ObjectId]
			AND i.[index_id] = ips.[IndexId]
			AND ips.[AvgFragmentationInPercent] BETWEEN 5
				AND @FragmentationLevel
		INNER JOIN [sys].[schemas] s ON t.[SchemaId] = s.[schema_id]

		SELECT @StatementID = MIN(SQLStatementID)
		FROM #SQLStatementsStore

		WHILE @StatementID IS NOT NULL
		BEGIN
			SELECT @SQLStatement = SQLstatement
			FROM #SQLStatementsStore
			WHERE SQLStatementID = @StatementID

			PRINT 'Executing...[' + @SQLStatement + '].'

			EXEC (@SQLStatement)

			DELETE
			FROM #SQLStatementsStore
			WHERE SQLStatementID = @StatementID

			SELECT @StatementID = MIN(SQLStatementID)
			FROM #SQLStatementsStore
		END

		SELECT [ObjectId]
			,[TableName]
			,[IndexId]
			,[IndexName]
			,[IndexPartitionNumber]
			,[IndexType]
			,[PadIndex]
			,[IndexFillFactor]
			,[StatisticsNoRecompute]
			,[IgnoreDuplicateKey]
			,[AllowRowLocks]
			,[AllowPageLocks]
			,[AvgFragmentationInPercent]
			,[CountRows]
		FROM #Index_Physical_Stats

		IF OBJECT_ID('Tempdb.dbo.#SQLStatementsStore') IS NOT NULL
			DROP TABLE #SQLStatementsStore

		IF OBJECT_ID('Tempdb.dbo.#PartitionTables ') IS NOT NULL
			DROP TABLE #PartitionTables

		IF OBJECT_ID('Tempdb.dbo.#Tables') IS NOT NULL
			DROP TABLE #Tables

		IF OBJECT_ID('Tempdb.dbo.#Index_Physical_Stats') IS NOT NULL
			DROP TABLE #Index_Physical_Stats
	END TRY

	BEGIN CATCH
		SELECT ERROR_NUMBER()
			,ERROR_SEVERITY()
			,ERROR_STATE()
			,ISNULL(ERROR_PROCEDURE(), @SQLProcedureName)
			,ERROR_LINE()
			,ERROR_MESSAGE()
	END CATCH
END
GO

Mark this procedure as system object by executing the following command so that this procedure can be accessed from any database:

USE [master]
GO

EXEC sys.sp_MS_marksystemobject sp_RebuildIndex;
GO

In the next version, I will add a logging feature so that you can see what indexes were defragmented, see how bad the fragmentation was, and perhaps do some trending on the data.

Let me know, if you find a bug or want me add any other feature.

Determine Index Fragmentation in a SQL Server Database

Index fragmentation can adversely affect query response time. When accessing data through an index, SQL Server must read each page in the specified range to retrieve the indexed values. If the index is highly fragmented, SQL Server may have to search many more pages, and possibly levels, to get this information. This results in poor performance and causes your application to respond slowly.

We can use system function sys.dm_db_index_physical_stats to detect fragmentation in specific index, all indexes in a table or indexed view, or all indexes in databases, or all indexes in all databases. The column avg_fragmentation_in_percent returns the percentage of fragmented data.

Although there is no hard and fast rule, a common recommendation is to keep the index fragmentation below 10 percent if possible. Following query can be used to identify indexes in the current database that have more than 5% fragmentation:

-- Ensure a USE statement has been executed first.

SELECT dm.[object_id]
	,DB_NAME(DB_ID()) + '.' + s.[name] + '.' + o.[name]
	,dm.[index_id]
	,i.[name]
	,dm.[partition_number]
	,dm.[index_type_desc]
	,[pad_index] = CASE i.[is_padded]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END
	,i.[fill_factor]
	,[statistics_norecompute] = CASE st.[no_recompute]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END
	,[ignore_dup_key] = CASE i.[ignore_dup_key]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END
	,[allow_row_locks] = CASE i.[allow_row_locks]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END
	,[allow_page_locks] = CASE i.[allow_page_locks]
		WHEN 0
			THEN 'OFF'
		WHEN 1
			THEN 'ON'
		END
	,dm.[avg_fragmentation_in_percent]
FROM sys.objects o
INNER JOIN sys.indexes i ON o.[object_id] = i.[object_id]
	AND i.[name] = 'NULL'
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') dm ON i.[object_id] = dm.[object_id]
	AND i.[index_id] = dm.[index_id]
	AND dm.[avg_fragmentation_in_percent] >= 5
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.stats st ON i.[name] = st.[name]
	AND o.[object_id] = st.[object_id]
	AND o.[type] = 'U'

Microsoft recommends reorganizing index if an index fragmentation is between 5-30% and rebuilding index if the index fragmentation is over 30%. For more details about reorganize and rebuild Indexes refer to Microsoft Books Online at http://msdn.microsoft.com/en-us/library/ms189858.aspx.