-- =============================================

 -- Author:    <Limy>

 -- Create date: <2015-12-31>

 -- Description:    <批量重建索引>

 -- EXEC pr_BatchRebuildIndex  'dbo','DatabaseLog','PK_DatabaseLog_DatabaseLogID',0

 -- =============================================

 alter proc pr_BatchRebuildIndex

 --@DataBase nvarchar(50)=null,--数据库名称，必输项

 @Scheme nvarchar(50)=null,--架构名称

 @TableName nvarchar(50)=null, --要重建索引的表名称,为空表示所有表

 @IndexName nvarchar(200)=null ,--要重建的索引名称

 @FragmentPercent decimal(10,5)=0 --碎片率为多少以上的药重建

 AS

 BEGIN

 DECLARE @Sql nvarchar(2000);

 DECLARE @RebuildSql nvarchar(2000);

 DECLARE @ERROR nvarchar(500);

 DECLARE @Tables table(TableName nvarchar(100), Indexid int,IndexName nvarchar(200), FragmentPercent decimal(10,5));

 ----判断数据库是否存在

 --IF DB_ID(@DataBase) is null

 --BEGIN

 --        RAISERROR('数据库不存在，请输入正确的数据库名称！',16,1);

 --        RETURN -1;

 --END 

 --判断架构是否存在

 IF isnull(@Scheme,'')<>'' and SCHEMA_ID(@Scheme) is null

 BEGIN

         RAISERROR('架构不存在，请输入正确的架构名称！',16,1);

         RETURN -1;

 END

 --表存不存在

 IF isnull(@TableName,'')<>''

 BEGIN

         --有架构

         IF isnull(@Scheme,'')<>'' and SCHEMA_ID(@Scheme) is null

         BEGIN

                 IF OBJECT_ID(@Scheme+'.'+@TableName) is null

                 BEGIN

                         SET @ERROR='表不存在，请输入正确的表名称！'

                         RAISERROR(@ERROR,16,2);

                         RETURN -1;

                 END

         END

         --没有架构

         IF not exists(select 1 from sys.tables as A  where A.name=@TableName   )

         BEGIN

                 SET @ERROR='表不存在，请输入正确的表名称！'

                 RAISERROR(@ERROR,16,5);

                 RETURN -1;

         END

 END

 --索引存不存在

 IF isnull(@IndexName,'')<>''

 BEGIN

         IF not exists(select 1 from sys.SysObjects where name=@IndexName and (xtype=N'UQ' OR xtype=N'PK') )

         BEGIN

                 RAISERROR('索引不存在，请输入正确的索引名称！',16,3);

                 RETURN -1;

         END

 END

 --如果表名为空表示所有表，如果索引为空表示所有索引

 --默认查询所有表，所有索引，所有百分比

 SET @Sql='SELECT D.name+''.''+object_name(a.object_id) [TableName] ,a.index_id ,B.name [IndexName] ,avg_fragmentation_in_percent

         FROM sys.dm_db_index_physical_stats ( DB_ID() , NULL , NULL, NULL, NULL ) AS A

         JOIN sys.indexes AS B ON a.object_id = b.object_id AND a.index_id = b.index_id

         inner JOIN sys.tables AS C ON a.object_id=C.object_id

         inner JOIN sys.schemas AS D ON C.schema_id=D.schema_id

         WHERE   a.index_id > 0 '

 IF isnull(@Scheme,'') <>''

 BEGIN

         SET @Sql +=' and D.name= '''+@Scheme+''' ';

 END

 IF  isnull(@TableName,'')<>''

 BEGIN

         SET @Sql +=' and object_name(a.object_id)= '''+@TableName+''' ';

 END

 IF  isnull(@IndexName,'')<>''

 BEGIN

         SET @Sql +=' and B.name= '''+@IndexName+''' ';

 END

 IF  isnull(@FragmentPercent,0)>0

 BEGIN

         SET @Sql +=' and avg_fragmentation_in_percent>= '+convert(nvarchar(10),@FragmentPercent)+' ';

 END

 INSERT INTO @Tables

 EXEC (@Sql)

 select '重建前',* from @Tables

 DECLARE cur cursor for

         select TableName,  IndexName from @Tables

 OPEN cur

 DECLARE @tbName NVARCHAR(100),@IXName NVARCHAR(200)

 FETCH NEXT FROM cur INTO @tbName,@IXName

 WHILE(@@fetch_status=0)

 BEGIN

         SET @RebuildSql='alter index ['+@IXName+'] on '+@tbName+' rebuild' --要加上[],否则索引里有空格会报错

         EXEC (@RebuildSql)

         FETCH NEXT FROM cur INTO @tbName,@IXName

 END

 CLOSE cur

 DEALLOCATE cur

 --重建后查询

 delete @Tables

 INSERT INTO @Tables

 EXEC (@Sql)

 select '重建后',* from @Tables

 Print N'执行完毕！';

 return 0;

 END