查看表索引碎片，重建所有表索引-190903

 

----use database_name
---- 查看某个表的 索引情况 ，是否需要重建 看 扫描密度
--declare @table_id int
--set @table_id=object_id('MA_STORAGE_SURPLUS')
--dbcc showcontig(@table_id)
--dbcc dbreindex('MA_STORAGE_SURPLUS','',90)
-- 查看 索引碎片情况
/*

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
--AND avg_fragmentation_in_percent <> 0
ORDER BY object_name(a.object_id)

*/
-- 删除索引碎片,重建索引
            DECLARE @TEMP1 VARCHAR(88)
            DECLARE @sql VARCHAR(MAX)
            DECLARE CA1 CURSOR --创建 游标
            FOR
                    SELECT DISTINCT
                            a.name AS tabname --, h.name AS idname  -- ,avg_fragmentation_in_percent  --   ,h.object_id ,h.name
                    FROM    sys.objects AS a
                    RIGHT JOIN sys.indexes AS h
                            ON a.object_id = h.object_id
                    LEFT JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) avg_b
                            ON avg_b.object_id = h.object_id
                    WHERE   a.type <> 's'
                            AND h.name IS NOT NULL  -- 筛选要索引存在
                            AND avg_b.avg_fragmentation_in_percent <> 0 --AND A.[name] LIKE 'MA%'  --AND a.[name] NOT  LIKE '%_BAK_%'
                    ORDER BY A.[name]
                    
                    -- 启用游标      
            OPEN CA1
            FETCH NEXT FROM CA1 INTO @TEMP1
            WHILE ( @@FETCH_STATUS <> -1 )
                  BEGIN
                        SET @sql = ' DBCC DBREINDEX  (' + @TEMP1 + ','''',90)'
                        EXEC (@sql )
                        FETCH NEXT FROM CA1 INTO @TEMP1
                  END
            CLOSE CA1
            DEALLOCATE CA1
      END