T-SQL查询高级—SQL Server索引中的碎片和填充因子

关于索引的概念可以看看宋大牛的博客

T-SQL查询高级—SQL Server索引中的碎片和填充因子

整个数据库的索引很多，索引碎片多了，不可能一个个的去重建，都是重复性的工作，所以索性写了个存储过程，按范围需要重建索引。

-- =============================================
-- 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