sql server检测库里所有表的索引碎片
SELECT schema_name(T.schema_id) AS Schema_Name,T.Name AS Table_Name,I.name AS Index_Name,
I.type AS Index_Type,D.avg_fragmentation_in_percent AS avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_id('DBNAME'),null, null, null, null) AS D
INNER JOIN sys.indexes AS I WITH(NOLOCK) ON D.index_id=I.index_id AND D.object_id=I.object_id
INNER JOIN sys.tables AS T WITH(NOLOCK) ON T.object_id=D.object_id
WHERE I.type>0 AND T.is_ms_shipped=0 AND D.avg_fragmentation_in_percent>=30
order by D.avg_fragmentation_in_percent desc
SQL Server 重建所有表索引方法
USE My_Database;
DECLARE @name varchar(100)
DECLARE authors_cursor CURSOR FOR
Select [name] from sysobjects where xtype='u' order by id
OPEN authors_cursor
FETCH NEXT FROM authors_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX (@name, '', 90)
FETCH NEXT FROM authors_cursor
INTO @name
END
deallocate authors_cursor
