Reindex all tables which are highly fragmented in the database

A common database maintenance activity, we need to Reindex database tables to maintain less fragmentation. For performing this we can identify each and every object which has fragmentation reached our expectation level; instead I have designed 2 procedures to findout the fragmented tables and perform the reindex on all indexes available in identified tables.

Hope this will reduce your maintenance time of writing queries for reindexing.
You can choose MSDB to create this proc and change the content to refer actual databases


-- Identifying Fragmentation of Tables (1)

Create Proc Pr_View_Defragmentation (@database sysname, @Frag int)
as
Declare @dbid int
select @dbid = dbid from sys.sysdatabases where name = @database
select distinct xtype, name,object_name(object_id),* from sys.dm_db_index_physical_stats (@dbid,NULL,NULL,NULL,'SAMPLED')
a inner join sys.sysobjects b on a.object_id = id where xtype = 'u' and name not like '%sys%'
and avg_fragmentation_in_percent >= @frag

-- Perform Reindexing to decrease the Fragmentation (2)

Create Proc Pr_Alter_index (@database sysname, @frag int) as
--DECLARE @DATABASE SYSNAME
--DECLARE @FRAG INT
Declare @a int
Declare @b int
Declare @sql varchar(1000)
Declare @Table table(id int identity(1,1),Object varchar(100))
Declare @sql1 varchar(100)
--set @database = 'DATABASE'
--set @frag = 10
SEt @a = 1
select @B = count(DISTINCT object_id) from sys.dm_db_index_physical_stats (db_id(@database),NULL,NULL,NULL,'SAMPLED')
a inner join sys.sysobjects b on a.object_id = id where xtype = 'u' and name not like '%sys%'
and avg_fragmentation_in_percent >= @frag
insert into @table(object)
Select distinct object_name(object_id) from sys.dm_db_index_physical_stats (db_id(@database),NULL,NULL,NULL,'SAMPLED')
a inner join sys.sysobjects b on a.object_id = id where xtype = 'u' and name not like '%sys%'
and avg_fragmentation_in_percent >= @frag
while @a < @b
Begin
select @SQL1 = OBJECT FROM @TABLE where id = @a SET @SQL = 'ALTER INDEX ALL ON ' + @database + '..' + @sql1 + ' REBUILD WITH (ONLINE = ON)'
exec (@sql)
set @a = @a+1
End


exec pr_View_Defragmentation database, fragmentation percentage
exec Pr_Alter_index database,fragmentation percentage

Eg : exec pr_View_Defragmentation 'abcd', 70