Labels

Find fragmentation for all tables for all databases in server

Today one of my cullig requested the script to find fragmentation for all tables in all databases in the server. He used some scripts which were giving wrong data.

I already have 2 scripts available
  1. To find row count on all databases (click here)
  2. To find database space of all database in the server (click here)
And everybody has the script to find fragmentation, I simple merged it

Merged scripts output is working fine

I also updated the index for all scripts 

/*******************Script for Fragmentation in All databases *****************************/

Declare @Tbl table
(
--ServerName varchar(128),
DBName varchar(128), SchemaName varchar(128),
TableName varchar (100), IndexName varchar (100), FragPercent float,
IndexType tinyint,IsPrimaryKey bit
);

Insert into @Tbl
exec SP_MSforeachdb @command1 =
            'use [?];
                select  --@@Servername,
                        DB_NAME(),
                        sc.name as SchemaName,
                        object_name (s.object_id) as TableName,
                        I.name,
                        s.avg_fragmentation_in_percent,
                        I.type,
                        I.is_primary_key
                from sys.dm_db_index_physical_stats (DB_ID (), NULL, NULL, NULL, ''LIMITED'') as S
                    join sys.indexes as I on s.object_id = I.object_id and s.index_id = I.index_id
                    join sys.objects as O on s.object_id = O.object_id
                    join sys.schemas as sc on O.schema_id = sc.schema_id
                where  (I.name is not null)
                ORDER BY  avg_fragmentation_in_percent DESC'
select * from @Tbl
go

/***************************************************/






No comments:
Write comments

Please do not enter spam links

Services

More Services