/* 描述：只显示逻辑碎片率大于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
