ÿþUSE [msdb]

GO

create PROCEDURE [dbo].[Usp_RebuildIndexes_SQL2005] 



/*	

	Description		: Rebuild or Reorganizes indexes according to level of fragementation and density



    Example:

          EXEC sp_RebuildIndexes_SQL2005 @maxfrag=75.0, @maxdensity=90.0, @databasename='YourDB'

*/







(

	@maxfrag		float = 75.0,  -- Maximum level of acceptable fragmentation 

	@maxdensity		float = 90.0,  -- Minimum level of acceptable density

	@databasename	varchar(355)   -- Database name

)



AS 





SET NOCOUNT ON;

DECLARE @schemaname sysname;

DECLARE @objectname sysname;

DECLARE @indexname sysname;

DECLARE @indexid int; 

DECLARE @currentfrag float;

DECLARE @currentdensity float;

DECLARE @partitionnum varchar(10);

DECLARE @partitioncount bigint;

DECLARE @indextype varchar(18);

DECLARE @updatecommand varchar(max);

DECLARE @command varchar(max);

DECLARE @page_locks int; 

DECLARE @has_lob int; 

DECLARE @db_id int;

DECLARE @IndexRebuildLogID BIGINT;

Declare @object_type char(1);

Declare @server_edition bit; --1 Enterprise , 0 other



-- Ensure the temporary table does not exist

IF (SELECT object_id('tempdb..#IndexToDefrag')) IS NOT NULL

	DROP TABLE #IndexToDefrag;



CREATE TABLE #IndexToDefrag(

	IndexID int not null

	, IndexName varchar(255) null

	, TableName varchar(255) null

	, Tableid int not null

	, SchemaName varchar(255) null

	, IndexType varchar(18) not null

	, PartitionNumber varchar(18) not null

	, PartitionCount int null

	, CurrentDensity float not null

	, CurrentFragmentation float not null

	, page_locks int null

	, Has_lob int not null default 0

	,object_type varchar(1)



);



-- Select @databasename , db_id(@databasename);



IF(db_id(@databasename) is NULL)

BEGIN

	PRINT @databasename + ' is not a valid database name' 

	return;

END



-- Get server edition to see if Online can be used



select @server_edition = case when cast (SERVERPROPERTY ('edition') as nvarchar(200)) like '%enterprise %' then 1 else 0 end



-- Insert index information for indexes that need defrag:



INSERT INTO #IndexToDefrag(

	IndexID, Tableid,  IndexType, PartitionNumber, CurrentDensity, CurrentFragmentation

	)

	SELECT

		fi.index_id 

		, fi.object_id 

		, fi.index_type_desc AS IndexType

		, cast(fi.partition_number as varchar(10)) AS PartitionNumber

		, fi.avg_page_space_used_in_percent AS CurrentDensity

		, fi.avg_fragmentation_in_percent AS CurrentFragmentation

	FROM sys.dm_db_index_physical_stats(db_id(@databasename), NULL, NULL, NULL, 'SAMPLED') AS fi 

	WHERE	(fi.avg_fragmentation_in_percent >= @maxfrag 	OR		fi.avg_page_space_used_in_percent < @maxdensity) And

			page_count > 64

	AND		fi.index_id > 0



--select * from #IndexToDefrag



--Assign the index names, schema names, table names and partition counts

SET @updatecommand = 'UPDATE #IndexToDefrag SET TableName = o.name, SchemaName = s.name, IndexName = i.Name ,page_locks = i.allow_page_locks,object_type=o.type

	,PartitionCount = (SELECT COUNT(*) pcount	

	FROM ' 

	+ QUOTENAME(@databasename) + '.sys.Partitions p

	where  p.Object_id = w.Tableid 

	AND p.index_id = w.Indexid)

	,Has_lob=isnull ((select top 1 col.user_type_id from '+ QUOTENAME(@databasename)+'.sys.columns col INNER JOIN ' + QUOTENAME(@databasename) + '.sys.types ty ON col.user_type_id = ty.user_type_id WHERE col.object_id=w.Tableid   AND (ty.name IN(''xml'',''image'',''text'',''ntext'') OR (ty.name IN(''varchar'',''nvarchar'',''varbinary'') AND col.max_length = -1))) ,0)

	FROM ' 

	+ QUOTENAME(@databasename) + '.sys.objects o INNER JOIN '

	+ QUOTENAME(@databasename) + '.sys.schemas s ON o.schema_id = s.schema_id 

	INNER JOIN #IndexToDefrag w ON o.object_id = w.tableid INNER JOIN '

	+ QUOTENAME(@databasename) + '.sys.indexes i ON w.tableid = i.object_id and w.indexid = i.index_id



';



	--print @updatecommand

	--select * from #IndexToDefrag



	EXEC(@updatecommand)



--Declare the cursor for the list of tables, indexes and partitions to be processed.



DECLARE rebuildindex CURSOR FOR 

	SELECT	

			object_type,

			has_lob ,

			page_locks,

             QUOTENAME(IndexName) AS IndexName

			, TableName

			, SchemaName

			, IndexType

			, PartitionNumber

			, PartitionCount

			, CurrentDensity

			, CurrentFragmentation

	FROM	#IndexToDefrag i 

	ORDER BY TableName, IndexID;



-- Open the cursor.

OPEN rebuildindex;



-- Loop through the tables, indexes and partitions.

FETCH NEXT

   FROM rebuildindex

   INTO @object_type,@has_lob,@page_locks,@indexname, @objectname, @schemaname, @indextype, @partitionnum, @partitioncount, @currentdensity, @currentfrag;



WHILE @@FETCH_STATUS = 0

	BEGIN



if @page_locks = 1



BEGIN



	SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + QUOTENAME(@databasename) +'.' + QUOTENAME(@schemaname) + '.' + QUOTENAME(@objectname);



	-- If the index is more heavily fragmented, issue a REBUILD.  Otherwise, REORGANIZE.

	IF @currentfrag >= 30 and @object_type <> 'v' 

				BEGIN;

			

				SELECT @command = @command + ' REBUILD ';

	--we can REORGANIZE a single index partition but cannot REBUILD a single index partition ONLINE.  			

				If @has_lob = 0 and @partitioncount <= 1 and @server_edition = 1

				SELECT @command = @command + ' WITH(ONLINE = ON) ';



				IF @partitioncount > 1

					SELECT @command = @command + ' PARTITION=' + @partitionnum;

				END;			



	IF @currentfrag < 30 or  @object_type ='V'

				BEGIN;

				SELECT @command = @command + ' REORGANIZE ';

				IF @partitioncount > 1

					SELECT @command = @command + ' PARTITION=' + @partitionnum;

				END;



		

		--print @command

		EXEC (@command);

END

		

		FETCH NEXT FROM rebuildindex INTO @object_type,@has_lob,@page_locks,@indexname, @objectname, @schemaname, @indextype, @partitionnum, @partitioncount, @currentdensity, @currentfrag;

	END;

-- Close and deallocate the cursor.

CLOSE rebuildindex;

DEALLOCATE rebuildindex;