Checking fragmented indexes

-- This script, as is, generates of list of index defrag candidates,
-- current threshhold at 10%, on a target database with default schema
-- of dbo.
use msdb
go
create procedure check_fragmented_indexes
 @dbname sysname,
 @schemaname sysname = 'dbo'
as
create table ##fragmented_indexes
(
 schemaname varchar(128),
 dbname varchar(128),
 tablename varchar(128),
 index_name varchar(128),
 fragmentation float,
 numpages int
)

create table ##schema_table
(
 schemaname varchar(128),
 tablename varchar(128)
)
declare @query varchar(5000)

select @query = 'use ' + @dbname + ';
 insert into ##fragmented_indexes(dbname, tablename, index_name, fragmentation, numpages)
 SELECT db_name(ps.database_id), object_name(ps.OBJECT_ID),
 b.name, ps.avg_fragmentation_in_percent, ps.page_count
 FROM sys.dm_db_index_physical_stats (db_id(''' +@dbname+ '''), NULL, NULL, NULL, ''LIMITED'') AS ps
 INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
 AND ps.index_id = b.index_id
 WHERE ps.database_id = DB_ID() and avg_fragmentation_in_percent > 10 and b.name <> ''NULL''
 ORDER BY object_name(ps.OBJECT_ID);

 insert into ##schema_table
 select s.name, t.name from sys.tables t
 left join sys.schemas s on (s.schema_id = t.schema_id)
 order by s.name, t.name
'

exec(@query)

update ##fragmented_indexes
 set schemaname = (select schemaname from ##schema_table where ##schema_table.tablename = ##fragmented_indexes.tablename)

select 'ALTER INDEX ' + index_name + ' ON ' + quotename(schemaname) + '.' + quotename(tablename) + ' REORGANIZE;'
 from ##fragmented_indexes

drop table ##fragmented_indexes
drop table ##schema_table

go
exec check_fragmented_indexes 'master' -- target DB goes here
go
drop proc check_fragmented_indexes