I already have 2 scripts available
- To find row count on all databases (click here)
- 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 commentsPlease do not enter spam links