SQL Server 2005:

/*
描述：只显示逻辑碎片率大于5%的索引信息
限制：针对SqlServer2005以后版本。
功能：对数据库服务器所有非系统数据库进行索引碎片检查
  返回碎片率>5%的索引信息
*/
create proc p_dba_manage_get_index_defrage
as
set nocount on
if not exists(select 1 from sys.tables where name = 'dba_manage_index_defrag')
create table dba_manage_index_defrag
([db_name] varchar(255)
,[table_name] varchar(255)
,[index_name] varchar(255)
,avg_fragmentation_in_percent real
,write_time datetime default getdate()
)
declare @db_name nvarchar(40)
set @db_name = ''
 
declare cur_db_name cursor for
 select
  name
 from
  sys.databases
 where
  database_id > 4 and state = 0
open cur_db_name
fetch cur_db_name into @db_name
while (@@fetch_status=0)
begin
 
 insert into dba_manage_index_defrag
   ([db_name]
   ,table_name
   ,index_name
   ,avg_fragmentation_in_percent)
 SELECT
   db_name(a.database_id) [db_name],
   c.name [table_name],
   b.name [index_name],
   a.avg_fragmentation_in_percent
 FROM
   sys.dm_db_index_physical_stats (DB_ID(@db_name), null,NULL, NULL, 'Limited') AS a
 JOIN
   sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
 join
   sys.tables as c on a.object_id = c.object_id
 where
  a.index_id>0
  and a.avg_fragmentation_in_percent>5
fetch next from cur_db_name into @db_name
end
CLOSE cur_db_name
DEALLOCATE cur_db_name
GO
select * from dba_manage_index_defrag –查看结果

　　b) 根据索引碎片的情况自动选择合适的处理方法

　　针对Sql Server2000的联机维护：

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(128);
DECLARE @execstr   varchar(255);
DECLARE @objectid  int;
DECLARE @indexid   int;
DECLARE @frag      decimal;
DECLARE @maxfrag   decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA+'.'+TABLE_NAME --MSDN上面直接使用TABLE_NAME，如果SCHEMA不是DBO就会出错
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table.
CREATE TABLE #fraglist (
   ObjectName char(255),
   ObjectId int,
   IndexName char(255),
   IndexId int,
   Lvl int,
   CountPages int,
   CountRows int,
   MinRecSize int,
   MaxRecSize int,
   AvgRecSize int,
   ForRecCount int,
   Extents int,
   ExtentSwitches int,
   AvgFreeBytes int,
   AvgPageDensity int,
   ScanDensity decimal,
   BestCount int,
   ActualCount int,
   LogicalFrag decimal,
   ExtentFrag decimal);

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;

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

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';
   EXEC (@execstr);

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END;

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

-- Delete the temporary table.
DROP TABLE #fraglist;
GO