SQL Server 2005 REBUILD\REORGANIZE Index using DMV's

--INDEXREBUILD 'ADVENTUREWORKS',50,90,5000,'REBUILD','OFF'
--INDEXREBUILD 'ADVENTUREWORKS',30,60,5000,'REORGANIZE','OFF'

Alter PROC INDEXREBUILD
@DBNAME VARCHAR(100), -- Name of database
@MIN_FRAG INT, -- Minimum fragmentation value
@MAX_FRAG INT, -- Maximum fragmentation value
@TABSIZE BIGINT, -- Table size in pages
@INDEXTYPE VARCHAR(10), -- REBUILD or REORGANIZE
@REBUILDOPT VARCHAR(10) = 'OFF' -- ONLINE REUILD option (available only for Enterprise editions)
AS
SET NOCOUNT ON
BEGIN
DECLARE @INDEXID INT;
DECLARE @PARTITIONNUM BIGINT;
DECLARE @PARTITIONS BIGINT;
DECLARE @FRAG FLOAT;
DECLARE @COMMAND NVARCHAR(4000);
DECLARE @SQLSTRING NVARCHAR(1000);
DECLARE @PARMDEFINITION NVARCHAR(2000);
DECLARE @DBID INT
DECLARE @PARTITIONCOUNT BIGINT;
DECLARE @SCHEMANAME NVARCHAR(130);
DECLARE @OBJECTNAME NVARCHAR(130);
DECLARE @INDEXNAME NVARCHAR(130);
DECLARE @OBJECTID INT;


CREATE TABLE #INDEXSTATS
(
OBJNAME VARCHAR(130),
IDXNAME VARCHAR(130),
SCHNAME VARCHAR(130),
AVG_FRAGMENTATION_IN_PERCENT FLOAT,
TBLSIZE BIGINT
)

SET @COMMAND = 'SELECT O.NAME,
I.NAME,
S.NAME,
AVG_FRAGMENTATION_IN_PERCENT,
SUM(TOTAL_PAGES)
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS ('+ CAST(DB_ID(@DBNAME) AS VARCHAR(3)) +', NULL, NULL, NULL, NULL) V
JOIN ['+ @DBNAME +'].SYS.OBJECTS AS O ON V.OBJECT_ID = O.OBJECT_ID
JOIN ['+ @DBNAME +'].SYS.SCHEMAS AS S ON S.SCHEMA_ID = O.SCHEMA_ID
JOIN ['+ @DBNAME +'].SYS.INDEXES AS I ON I.OBJECT_ID = O.OBJECT_ID
AND V.INDEX_ID = I.INDEX_ID
JOIN ['+ @DBNAME +'].SYS.PARTITIONS AS P ON P.OBJECT_ID = O.OBJECT_ID
JOIN ['+ @DBNAME +'].SYS.ALLOCATION_UNITS A ON P.PARTITION_ID = A.CONTAINER_ID
WHERE AVG_FRAGMENTATION_IN_PERCENT >= '+ CAST(@MIN_FRAG AS VARCHAR(8)) + '
AND AVG_FRAGMENTATION_IN_PERCENT <= '+ CAST(@MAX_FRAG AS VARCHAR(8)) + '
AND I.INDEX_ID > 0
GROUP BY O.NAME,
I.NAME,
S.NAME,
AVG_FRAGMENTATION_IN_PERCENT
HAVING SUM(TOTAL_PAGES) >= ' + CAST(@TABSIZE AS VARCHAR(50)) + ''

INSERT INTO #INDEXSTATS
(
OBJNAME,
IDXNAME,
SCHNAME,
AVG_FRAGMENTATION_IN_PERCENT,
TBLSIZE
)
EXEC(@COMMAND)

SET @COMMAND = ''

DECLARE TAB CURSOR FOR
SELECT OBJNAME,
IDXNAME,
SCHNAME
FROM #INDEXSTATS;

OPEN TAB;

FETCH NEXT
FROM TAB
INTO @OBJECTNAME, @INDEXNAME, @SCHEMANAME;

WHILE @@FETCH_STATUS = 0
BEGIN;
IF @INDEXTYPE = 'REBUILD'
BEGIN
IF @REBUILDOPT = 'ONLINE'
SET @COMMAND = N'ALTER INDEX [' + @INDEXNAME + N'] ON [' + @DBNAME + N'].[' + @SCHEMANAME + N'].[' + @OBJECTNAME + N'] REBUILD WITH (FILLFACTOR = 90,ONLINE = ON)';
ELSE
SET @COMMAND = N'ALTER INDEX [' + @INDEXNAME + N'] ON [' + @DBNAME + N'].[' + @SCHEMANAME + N'].[' + @OBJECTNAME + N'] REBUILD WITH (FILLFACTOR = 90,ONLINE = OFF)';
END
ELSE
SET @COMMAND = N'ALTER INDEX [' + @INDEXNAME + N'] ON [' + @DBNAME + N'].[' + @SCHEMANAME + N'].[' + @OBJECTNAME + N'] REORGANIZE';

SELECT'EXECUTING: ' + @COMMAND;
print @COMMAND
--EXEC (@COMMAND);

FETCH NEXT
FROM TAB
INTO @OBJECTNAME, @INDEXNAME, @SCHEMANAME;
END;

CLOSE TAB;
DEALLOCATE TAB;

DROP TABLE #INDEXSTATS
END


------
To run this SP against All Database use below cursor.

--Cursor for runing the procedure on all database.


Declare @DbName varchar(100)
Declare @cmd varchar(100)

Declare Reindex_cur cursor for

select name from sysdatabases where dbid>4
open Reindex_cur fetch next from Reindex_cur into @DbName
while (@@fetch_status = 0)

begin

Set @cmd='INDEXREBUILD '''+@DbName+''',50,90,5000,''REBUILD'',''ONLINE'''

EXEC( @cmd)

fetch next from Reindex_cur into @DbName

end
close Reindex_cur
deallocate Reindex_cur