重建索引

 1 use DATABASE_NAME; 2  3 DECLARE @TableName VARCHAR(255) 4 DECLARE @sql NVARCHAR(500) 5 DECLARE @fillfactor INT 6 SET @fillfactor = 80 7 DECLARE TableCursor CURSOR FOR 8 SELECT OBJECT_SCHEMA_NAME([object_id])+‘.[‘+name+‘]‘ AS TableName 9 FROM sys.tables w10 OPEN TableCursor11 FETCH NEXT FROM TableCursor INTO @TableName12 WHILE @@FETCH_STATUS = 013 BEGIN14 SET @sql = ‘ALTER INDEX ALL ON ‘ + @TableName + ‘ REBUILD WITH (ONLINE=off,FILLFACTOR=80);‘ --‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)‘15 exec (@sql)16 FETCH NEXT FROM TableCursor INTO @TableName17 END18 CLOSE TableCursor19 DEALLOCATE TableCursor20 GO

 查询索引碎片

use DATABASE_NAME;SELECT dbschemas.[name] as ‘Schema‘, dbtables.[name] as ‘Table‘, dbindexes.[name] as ‘Index‘,indexstats.alloc_unit_type_desc,indexstats.avg_fragmentation_in_percent,indexstats.page_countFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstatsINNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]AND indexstats.index_id = dbindexes.index_idWHERE indexstats.database_id = DB_ID() and dbindexes.[name] not like ‘pk_%‘ORDER BY indexstats.avg_fragmentation_in_percent * page_count desc