Yet another way to defrag indexes

CREATE PROCEDURE [dbo].[usp_defrag_indexes]
	@dbname sysname, @fillfactor int = 80, @fragthreshold int = 30
AS
	declare @query nvarchar(max)
	if (DB_ID(@dbname)) is NULL
	begin
		print 'Invalid database name.'
		return 0
	end
	if @dbname = 'tempdb'
	begin
		print 'TEMPDB cannot be indexed reliably.'
		return 0
	end
	if (select COUNT(*) from master.dbo.sysdatabases where name = @dbname and cmptlevel < 90) > 0
	begin
		print 'Only SQL 2005 databases and up allowed.'
		return 0
	end
	

	print '----- Beginning defragging of: ' + quotename(@dbname) + ' at '
		+ CONVERT(varchar(19), getdate(), 121);
	if (@fillfactor < 0 or @fillfactor > 100)		-- defaults to common 80% fillfactor
		set @fillfactor = 80
	if (@fragthreshold < 0 or @fragthreshold > 100)	-- prevents silliness
		set @fragthreshold = 30
	set @query =
	'USE ' + QUOTENAME(@dbname) + ';
	SET NOCOUNT ON;
	DECLARE @objectid int;
	DECLARE @indexid int;
	DECLARE @partitioncount bigint;
	DECLARE @schemaname nvarchar(130); 
	DECLARE @objectname nvarchar(130); 
	DECLARE @indexname nvarchar(130); 
	DECLARE @partitionnum bigint;
	DECLARE @partitions bigint;
	DECLARE @frag float;
	DECLARE @command nvarchar(4000); 
	DECLARE @lock_esc int;
	
	-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
	-- and convert object and index IDs to names.

	SELECT
		A.object_id AS objectid,
		A.index_id AS indexid,
		A.partition_number AS partitionnum,
		A.avg_fragmentation_in_percent AS frag,
		B.lock_escalation AS lock_escalation
	INTO #work_to_do
	FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'') as A
		inner join sys.tables as B on B.object_id = A.object_id
	WHERE A.avg_fragmentation_in_percent >= 10.0 AND A.index_id > 0 and A.page_count > 500

	-- Declare the cursor for the list of partitions to be processed.
	DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

	-- Open the cursor.
	OPEN partitions;

	-- Loop through the partitions.
	WHILE (1=1)
		BEGIN;
			FETCH NEXT
			   FROM partitions
			   INTO @objectid, @indexid, @partitionnum, @frag, @lock_esc;
			IF @@FETCH_STATUS < 0 BREAK;
			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 @partitioncount > 1 and @frag < ' +cast(@fragthreshold as varchar(3)) + '
			SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + 
				N'' REORGANIZE PARTITION='' + CAST(@partitionnum AS nvarchar(10));
			ELSE
			IF @partitioncount > 1 and @frag >= ' + cast(@fragthreshold as varchar(3)) + '
			SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + 
				N'' REBUILD PARTITION='' + CAST(@partitionnum AS nvarchar(10));
			ELSE 
			IF @frag < ' +cast(@fragthreshold as varchar(3)) + '
			SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + 
				N'' REORGANIZE'';
			ELSE				
			IF @frag >= ' +cast(@fragthreshold as varchar(3)) + '
			SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + 
				N'' REBUILD WITH (ONLINE=ON, FILLFACTOR = ' + cast(@fillfactor as varchar(3)) + ')'';
				
			IF CAST(SERVERPROPERTY(''Edition'') AS VARCHAR(MAX)) LIKE ''%Standard%''
				SET @command = REPLACE(@command, ''ONLINE=ON'', ''ONLINE=OFF'')

			IF @lock_esc <> 0 -- if lock escalation is disabled, a reorganize is not possible, so this fixes it 
				SET @command = REPLACE(@command, ''REORGANIZE'', ''REBUILD'');
				
			BEGIN TRY
				EXEC (@command);
				PRINT N''Executed: '' + @command;
			END TRY
			BEGIN CATCH -- if the rebuild fails, generally because ONLINE=ON was a problem
				IF @command like ''%line[_]item %''
					SET @command = REPLACE(@command, ''REORGANIZE'', ''REBUILD'')
				SET @command = REPLACE(@command, ''ONLINE=ON'', ''ONLINE=OFF'')
				exec (@command)
				print N''Executed (CATCH with ONLINE = OFF): '' + @command
			END CATCH
		END

	-- Close and deallocate the cursor.
	CLOSE partitions;
	DEALLOCATE partitions;

	-- Drop the temporary table.
	DROP TABLE #work_to_do;'
	-- print (@query)
	exec (@query)

	print '----- Ending defragging of: ' + quotename(@dbname) + ' at '
		+ CONVERT(varchar(19), getdate(), 121);
	print '';
GO