Cacher is the code snippet organizer for pro developers

USE [somedatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[p_Fix_Indexes]
AS
BEGIN
	SET NOCOUNT ON;

	-- Reorganize Indexes at 10% Fragmentation
	DECLARE @ReorgThreshold float; 
	SET @ReorgThreshold = 10.0;

	-- Rebuild Indexes at 30% Fragmentation
	DECLARE @RebuildThreshold float;
	SET @RebuildThreshold = 30.0;

	-- Only Rebuild Indexes on tables with 50 Rows
	DECLARE @RowThreshold smallint;
	SET @RowThreshold = 50;
	
	DECLARE @objectId int;  
	DECLARE @indexId int;  
	DECLARE @partitionCount bigint;  
	DECLARE @schemaName nvarchar(300);  
	DECLARE @objectName nvarchar(300);  
	DECLARE @indexName nvarchar(300);  
	DECLARE @partitionNum bigint;  
	DECLARE @partitions bigint;  
	DECLARE @frag float;  
	DECLARE @pageCount int;  
	DECLARE @command nvarchar(4000);

	BEGIN TRY
		-- Get Index Statistics
		SELECT object_id AS objectid,  
				index_id AS indexid,  
				partition_number AS partitionnum,  
				avg_fragmentation_in_percent AS frag,  
				page_count AS page_count  
			INTO #temporary_storage 
			FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')  
			WHERE avg_fragmentation_in_percent > @ReorgThreshold  
				AND index_id > 0  
				AND page_count > @RowThreshold;

		DECLARE partitions CURSOR FOR SELECT * FROM #temporary_storage;
		OPEN partitions;

		WHILE(@@FETCH_STATUS=0)
		BEGIN
			FETCH NEXT FROM partitions
				INTO @objectId, @indexId, @partitionNum, @frag, @pageCount;
			
			SELECT @objectname = QUOTENAME(o.name), @schemaName = QUOTENAME(s.name)  
				FROM sys.objects AS o  
				JOIN sys.schemas as s ON s.schema_id = o.schema_id  
				WHERE o.object_id = @objectId;

			SELECT @indexname = QUOTENAME(name)  
				FROM sys.indexes  
				WHERE object_id = @objectId AND index_id = @indexId;

			SELECT @partitioncount = count (*)  
				FROM sys.partitions  
				WHERE object_id = @objectId AND index_id = @indexId;
			
			IF @frag > @ReorgThreshold AND @frag < @RebuildThreshold
				BEGIN 
					SET @command = N'ALTER INDEX ' + @indexName + N' ON ' + @schemaName + N'.' + @objectName + N' REORGANIZE';
					
					IF @partitionCount > 1  
						SET @command = @command + N' PARTITION=' + CAST(@partitionNum AS nvarchar(10));

					EXEC (@command);
				END
			ELSE IF @frag > @RebuildThreshold
				BEGIN
					SET @command = N'ALTER INDEX ' + @indexName + N' ON ' + @schemaName + N'.' + @objectName + N' REBUILD';

					IF @partitioncount > 1  
						SET @command = @command + N' PARTITION=' + CAST(@partitionNum AS nvarchar(10));

					EXEC (@command);
				END
		END
		CLOSE partitions;
		DEALLOCATE partitions;
		DROP TABLE #temporary_storage;
	END TRY
	BEGIN CATCH
		-- do nothing, but something is wonky...
	END CATCH;
END;