SQL Fragmentation - lightweight script

The following script will iterate through all of your databases and report on the average fragmentation of your indexes for each DB.
Its less intrusive as it doesn't create any temporary tables and gives you a quick report on where fragmentation is most prevalent

DECLARE @DatabaseID smallint
DECLARE @DatabaseName varchar(255)  
DECLARE DatabaseCursor CURSOR FOR 
SELECT  dbid, name FROM master.dbo.sysdatabases  
--WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')  
--ORDER BY 1 
OPEN DatabaseCursor 
FETCH NEXT FROM DatabaseCursor INTO @DatabaseID, @DatabaseName
WHILE @@FETCH_STATUS = 0 
BEGIN 
-----------------------------------------------------
  SELECT @DatabaseName as Database_Name, avg(avg_fragmentation_in_percent) as Average_Fragmentation FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
  where avg_fragmentation_in_percent > 30 and database_id =  @DatabaseID
  
----------------------------------------------------- 
    
FETCH NEXT FROM DatabaseCursor INTO @DatabaseID,@DatabaseName
END 
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor