I have coded this "index management routine" and used for over two years. Commercial vendor is selling Defrag Manager for $8400 per host-year. It does the same.
The code uses DMV frag % value and row-count to determine if a particular index needs a defrag, if so it builds 2008/2005 command by syntax "alter index .... rebuild...." and executes it.
I use a wrapper code like below to call this routine from SQL Agent job every Sunday and run a loop thru all DBs (exclude the ones you don't want).
I am sharing with you for the benefits I got from sqlservercentral.com. It is my little contribution back to community.
If you cann't get it to work, please email me.
USE ADMINDB
SET NOCOUNT ON
DECLARE @dbName varchar(255), @rc int
SELECT name
INTO #db
FROM sys.databases
WHERE
name NOT IN ('master', 'model', 'msdb', 'tempdb', 'admindb') AND
name NOT LIKE '%ReportServer%' AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
SELECT @rc = 1, @dbName = MIN(name)
FROM #db
WHILE @rc <> 0
BEGIN
EXEC ADMINDB.dbo.isp_ALTER_INDEX
@dbName = @dbName,
@statsMode = 'SAMPLED',
@defragType = 'REBUILD',
@minFragPercent = 70,
@maxFragPercent = 100,
@minRowCount = 1000
SELECT TOP 1 @dbName = name
FROM #db
WHERE name > @dbName
ORDER BY name
SET @rc = @@ROWCOUNT
END
DROP TABLE #db
