Index health and defrag (SQL Server 2k2)

I use this when setting up scheduled maintenance supblans for reorganizing/rebuilding indices (where/when required). A bit more sophisticated and configurable than just adding a Reorganize/Rebuild Index Task which does this job mindlessly :)

Code:

$PROMPT(operation,Show\,Reorganize or Rebuild?,,,0 1 2,Show Reorganize Rebuild)$
$PROMPT(lower_limit,Lower limit of fragmentation?
Recommended values:
Rebuild : 30%
Reorganize : 10%,,,0 10 20 30 40 50)$
$PROMPT(upper_limit,Upper limit of fragmentation?
Recommended values:
Rebuild : 100%
Reorganize : 30%,8,,20 30 40 50 60 70 80 90 100)$
$PROMPT(min_page_count,Minimum number of pages?,200)$
$PROMPT(limit,Limit to TOP x?
(0 = no limit),0)$
$PROMPT(simonly,Simulate only?,,,"PRINT @sql" EXEC(@sql),"Yes\,print only." "No\,go for it!")$

USE $DB$
GO

DECLARE @lower_limit INT = $lower_limit$
   ,@upper_limit INT = $upper_limit$
   ,@min_page_count INT = $min_page_count$

DECLARE @sql NVARCHAR( MAX ) = '', @dbid INT = DB_ID()
SELECT $$SELECT CASE WHEN $limit$ = 0 THEN '' ELSE 'TOP $limit$' END$$
$$
SELECT CASE $operation$
WHEN 0 THEN
' s.name AS schema_name
,t.name AS table_name
,i.name AS index_name
,i.type AS index_type
,i.type_desc AS index_type_desc
,i.is_disabled AS is_disabled
,ddips.avg_fragmentation_in_percent AS avg_frag
,ddips.index_depth AS index_depth
,ddips.page_count AS index_page_count'
WHEN 1 THEN
' @sql = @sql
+ ''ALTER INDEX '' + QUOTENAME( i.name ) + '' ON '' + QUOTENAME( s.name ) + ''.'' + QUOTENAME( t.name ) + '' REORGANIZE WITH ( LOB_COMPACTION = ON )''
+ '' -- avg fragmentation : '' + CAST( ddips.avg_fragmentation_in_percent AS VARCHAR( 64 ) ) + ''%, index page count : '' + CAST( ddips.page_count AS VARCHAR( 64 ) )
+ CHAR( 10 )'
WHEN 2 THEN
'@sql = @sql
+ ''ALTER INDEX '' + QUOTENAME( i.name ) + '' ON '' + QUOTENAME( s.name ) + ''.'' + QUOTENAME( t.name ) + '' REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )''
+ '' -- avg fragmentation : '' + CAST( ddips.avg_fragmentation_in_percent AS VARCHAR( 64 ) ) + ''%, index page count : '' + CAST( ddips.page_count AS VARCHAR( 64 ) )
+ CHAR( 10 )'
END
$$
FROM
sys.schemas s
INNER JOIN
sys.tables t
   ON t.schema_id = s.schema_id
INNER JOIN
sys.indexes i
   ON i.object_id = t.object_id
INNER JOIN
sys.dm_db_index_physical_stats( @dbid ,NULL ,NULL ,NULL ,NULL ) ddips
   ON ddips.[object_id] = i.[object_id]
   AND ddips.index_id = i.index_id
WHERE i.index_id >= 0
AND i.type IN ( 1 ,2 ) -- clustered & nonclustered only
AND i.is_hypothetical = 0
AND i.is_disabled = 0
AND ddips.avg_fragmentation_in_percent BETWEEN @lower_limit AND @upper_limit
AND ddips.page_count > @min_page_count
ORDER BY
ddips.avg_fragmentation_in_percent DESC
,ddips.page_count DESC

$simonly$