USE [master]
GO

/****** Object:  StoredProcedure [dbo].[usp_auto_indexdefrag_online]    Script Date: 02/07/2014 11:44:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE  PROCEDURE [dbo].[usp_auto_indexdefrag_online]
AS

BEGIN
SET NOCOUNT ON
 DECLARE @Db_name NVARCHAR(256)
   ,@SchemaName NVARCHAR(256)
   ,@TableName NVARCHAR(256)
   ,@IndexName NVARCHAR(512)
   ,@PctFrag DECIMAL
   ,@Defrag NVARCHAR(MAX)
   
    IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID =OBJECT_ID(N‘#tmp‘)) DROP TABLE #tmp;
    IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID =OBJECT_ID(N‘#tmp_sub‘)) DROP TABLE #tmp_sub;
    
CREATE TABLE #tmp_sub(database_id INT,dbname NVARCHAR(32),tablename NVARCHAR(128),index_type_desc NVARCHAR(128))
CREATE TABLE #tmp(database_id INT,dbname NVARCHAR(256),tablename NVARCHAR(256),indexname NVARCHAR(256),type_desc NVARCHAR(128),schemaname NVARCHAR(256),avgfragment DECIMAL)

------找出  text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)、xml 或大型 CLR 类型的列

EXEC sp_MSforeachdb ‘insert into #tmp_sub(database_id,dbname,tablename,index_type_desc)
select distinct c.database_id,‘‘?‘‘ dbname,b.name,‘‘CLUSTERED‘‘
 from 
         ?.sys.dm_db_index_physical_stats(DB_ID(‘‘?‘‘),NULL,NULL,NULL,‘‘SAMPLED‘‘) as a
    join ?.sys.tables as b on a.object_id=b.object_id 
    join sys.databases as c on a.database_id=c.database_id
    join ?.sys.all_columns d on d.object_id =a.object_id 
    join ?.sys.sysobjects e on d.object_id=e.id and e.xtype=‘‘U‘‘ 
    join ?.sys.types f on d.user_type_id=f.user_type_id 
where  b.type_desc=‘‘USER_TABLE‘‘ and b.is_ms_shipped=0 and (d.max_length =-1
    OR (f.name in (‘‘image‘‘,‘‘text‘‘,‘‘ntext‘‘,‘‘xml‘‘,‘‘varbinary‘‘,‘‘binary‘‘)))‘
    
----找出 所有库中的索引

EXEC sp_MSforeachdb ‘insert into #tmp(database_id,dbname,tablename,indexname,type_desc,schemaname,avgfragment)
select  distinct d.database_id,‘‘?‘‘ dbname,c.name,b.name,b.type_desc,e.name,a.avg_fragmentation_in_percent 
from ?.sys.dm_db_index_physical_stats(DB_ID(‘‘?‘‘),NULL,NULL,NULL,‘‘SAMPLED‘‘) 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 
    join sys.databases as d on a.database_id=d.database_id
    join ?.sys.schemas as e on c.schema_id=e.schema_id
    join ?.sys.sysobjects f on c.object_id=f.id
    join ?.sys.all_columns g on f.id=g.object_id
    join ?.sys.types h on g.user_type_id=h.user_type_id
    where a.avg_fragmentation_in_percent >20
     and c.type=‘‘U‘‘ and f.xtype=‘‘U‘‘ 
     and c.is_ms_shipped=0 ‘
     
 DECLARE frg_cur CURSOR FOR
  SELECT dbname,tablename,indexname,
         schemaname,avgfragment 
       FROM #tmp 
       WHERE NOT EXISTS (SELECT 1 FROM #tmp_sub b WHERE database_id=b.database_id AND tablename=b.tablename AND type_desc=b.index_type_desc)
    
  OPEN frg_cur
  FETCH NEXT FROM  frg_cur INTO @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag
  WHILE @@FETCH_STATUS=0
   BEGIN
    IF @PctFrag BETWEEN 20.0 AND 40.0
     BEGIN
      SET @Defrag=N‘ ALTER INDEX ‘+@IndexName+‘ ON ‘+@Db_name+‘.‘+@SchemaName+‘.‘+ @TableName +‘ REORGANIZE‘--重新组织索引页不删除索引
      EXEC SP_EXECUTESQL @Defrag
     END
     ELSE IF @PctFrag>40.0
     BEGIN
      SET @Defrag=N‘ ALTER INDEX ‘+@IndexName+‘ ON ‘+@Db_name+‘.‘+@SchemaName+‘.‘+ @TableName +‘ REBUILD WITH (ONLINE = ON )‘--联机重建索引。即不锁定表重新创建索引
      EXEC SP_EXECUTESQL @Defrag
     END
     FETCH NEXT FROM  frg_cur INTO @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag
   END
   CLOSE frg_cur
   DEALLOCATE frg_cur

END

SET NOCOUNT OFF



GO

EXEC [master].[dbo].[usp_auto_indexdefrag_online]