set nocount on
use bn
declare @dbid int select @dbid=db_id()
SELECT tb.name Table_name,ind.name Index_Name,round(avg_fragmentation_in_percent,2) 'Франметация%',page_count 'Кол-воСтраницИндекса', Tb2.row_count КолвоСтрокТаблицы
FROM sys.dm_db_index_physical_stats ( @dbid, null, NULL, NULL , 'LIMITED') as stat
inner join sys.indexes as ind on stat.index_id= ind.index_id and stat.object_id=ind.object_id
inner join sys.tables as Tb on Tb.object_id=ind.object_id
inner join sys.dm_db_partition_stats as Tb2 on Tb2.object_id=stat.object_id and tb2.index_id=stat.index_id
order by avg_fragmentation_in_percent desc
Скрипт "заточен" на базу данных BN. Если у вас база данных называется по другому - потребуется изменить код скрипта в части
DECLARE @tablename VARCHAR (128), @execstr VARCHAR (255),@objectid INT, @objectowner VARCHAR(255), @indexid INT,@frag DECIMAL
DECLARE @indexname CHAR(255),@dbname sysname, @tableid INT, @tableidchar VARCHAR(255)
SELECT @dbname = 'bn';
DECLARE tables CURSOR FOR
SELECT convert(varchar,so.id) FROM sysobjects so JOIN sysindexes si ON so.id = si.id WHERE so.type ='U' AND si.indid < 2 AND si.rows > 0;
CREATE TABLE #fraglist (ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255),IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL);
OPEN tables
FETCH NEXT FROM tables INTO @tableidchar
WHILE @@FETCH_STATUS = 0 BEGIN
INSERT INTO #fraglist EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT FROM tables INTO @tableidchar
END
CLOSE tables DEALLOCATE tables
SELECT ObjectNAme as TableName, IndexName, LogicaLFrag 'Фрагментация%',CountPages 'Кол-воСтраницИндекса',CountRows КолвоСтрокТаблицы FROM #fraglist
order by LogicaLFrag desc
drop table #fraglist
