SQL Server Rebuild Index

Use [msdb]
GO

/****** object:storedprocedure [dbo].    [Indexmaintain] Script date:6/14/2013 1:46:17 PM ******/
SET ANSI_NULLS on
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo]. [Indexmaintain]
As
SET NOCOUNT on

BEGIN TRY
DECLARE @EXCEPTION VARCHAR (MAX)
DECLARE @MailSubject NVARCHAR (255)
DECLARE @DBName NVARCHAR (255)
DECLARE @TableName NVARCHAR (255)
DECLARE @SchemaName NVARCHAR (255)
DECLARE @IndexName NVARCHAR (255)
DECLARE @avg_fragmentation_in_percent_old DECIMAL (18,3)
DECLARE @avg_page_space_used_in_percent_old DECIMAL (18,3)
DECLARE @avg_fragmentation_in_percent_new DECIMAL (18,3)
DECLARE @avg_page_space_used_in_percent_new DECIMAL (18,3)

DECLARE @Defrag NVARCHAR (max)
DECLARE @Sql NVARCHAR (max)
declare @ParmDefinition nvarchar (500)
Set @EXCEPTION = ' '

--Delete #frag
if exists (SELECT * from sys.objects where object_id=object_id (N ' #Frag '))
drop table #Frag

--Define temporary table #frag Save index Fragment
CREATE TABLE #Frag (
DBname NVARCHAR (255),
TableName NVARCHAR (255),
SchemaName NVARCHAR (255),
IndexName NVARCHAR (255),
Avgfragment DECIMAL (18,3),
avg_page_space_used DECIMAL (18,3)
)

--Remove Databass for READ_ONLY
DECLARE dbstatus cursor FOR
Select Name
From sys.databases
where DATABASEPROPERTYEX (name, ' updateability ') <> ' read_only ' and state_desc= ' ONLINE '
Open Dbstatus
FETCH NEXT from Dbstatus to @DBName
While @ @FETCH_STATUS =0
Begin
Set @sql = ' INSERT into #Frag (DBname, tablename,schemaname,indexname,avgfragment,avg_page_space_used)
Select ' [' [email protected]+ '] ' as DBName, ' ' [' +t.name+ '] ' as TableName, SC. Name as SchemaName, ' [' +i.name+ '] ' as IndexName, S.avg_fragmentation_in_percent, s.avg_page_space_used_in_percent
From '[email protected]+ '. Sys.dm_db_index_physical_stats (db_id ("[email protected]+ '), Null,null,null, ' Sampled ') as S
Join '[email protected]+ '. Sys.indexes i on s.object_id=i.object_id and s.index_id=i.index_id
Join '[email protected]+ '. Sys.tables T on i.object_id=t.object_id
Join '[email protected]+ '. Sys.schemas SC on T.SCHEMA_ID=SC. schema_id
where S.avg_fragmentation_in_percent >20 and t.type= "U" and S.page_count>8 and I.allow_page_locks=1 and I.allow_ Row_locks=1
ORDER by Tablename,indexname '
EXEC (@sql)
FETCH NEXT from Dbstatus to @DBName
End
Close Dbstatus
Deallocate Dbstatus

--Define Cursor traversal temporary table #frag, take different scheme to maintain index according to fragment size.
DECLARE cList CURSOR for
SELECT * FROM #Frag
Open CList
FETCH NEXT from CList to @DBName, @TableName, @SchemaName, @IndexName, @avg_fragmentation_in_percent_old, @avg_page_ Space_used_in_percent_old
While @ @FETCH_STATUS =0
Begin
--fragment between 20.0 and 40.0, defragment with Alter INDEX reorganize
If @avg_fragmentation_in_percent_old between 20.0 and 40.0
Begin
--Sorting out fragments
Set @Defrag =n ' Alter INDEX ' + '[email protected]+ ' on '[email protected]+ '. '[email protected]+ '. '[email protected]+ ' reorganize '
EXEC sp_executesql @Defrag

--Get the percentage of fragments of index being collated
Set @Sql =n ' use '[email protected]+ '; Select @avg_fragmentation_in_percent_new_temp =s.avg_fragmentation_in_percent, @avg_page_space_used_in_percent_ new_temp= s.avg_page_space_used_in_percent
From '[email protected]+ '. Sys.indexes I
INNER JOIN '[email protected]+ '. Sys.dm_db_index_physical_stats (db_id (replace (replace ("[email protected]+ ', ' [', ' ', ', '], ', ', '), object_id ("'[email protected]+ ' + '), Null,null, ' sampled ') as S on i.index_id=s.index_id
where i.object_id=object_id ("'[email protected]+ ' + ') and I.name= '[email protected]+ ""
Set @ParmDefinition =n ' @avg_fragmentation_in_percent_new_temp DECIMAL (18,3) output, @avg_page_space_used_in_percent _new_temp DECIMAL (18,3) Output '
EXEC sp_executesql @Sql, @ParmDefinition, @[email protected]_fragmentation_in_percent_new output, @[email protected]_ Page_space_used_in_percent_new output

--write Log
Insert [dbo]. Indexdefrag values (@DBName, @TableName, @SchemaName, @IndexName, GETDATE (), @avg_fragmentation_in_percent_old, @avg_ Page_space_used_in_percent_old, @avg_fragmentation_in_percent_new, @avg_page_space_used_in_percent_new, ' 0 ')
End
--fragment greater than 40.0, defragment with Alter INDEX rebuild
else if @avg_fragmentation_in_percent_old >40.0
Begin
--Sorting out fragments
Set @Defrag =n ' Alter INDEX ' + '[email protected]+ ' on '[email protected]+ '. '[email protected]+ '. '[email protected]+ ' rebuild '
EXEC sp_executesql @Defrag

--Get the percentage of fragments of index being collated
Set @Sql =n ' use '[email protected]+ '; Select @avg_fragmentation_in_percent_new_temp =s.avg_fragmentation_in_percent, @avg_page_space_used_in_percent _new_temp= s.avg_page_space_used_in_percent
From '[email protected]+ '. Sys.indexes I
INNER JOIN '[email protected]+ '. Sys.dm_db_index_physical_stats (db_id (replace (replace ("[email protected]+ ', ' [', ' ', ', '], ', ', '), object_id ("'[email protected]+ ' + '), Null,null, ' sampled ') as S on i.index_id=s.index_id
where i.object_id=object_id ("'[email protected]+ ' + ') and I.name= '[email protected]+ ""
Set @ParmDefinition =n ' @avg_fragmentation_in_percent_new_temp DECIMAL (18,3) output, @avg_page_space_used_in_percent _new_temp DECIMAL (18,3) Output '
EXEC sp_executesql @Sql, @ParmDefinition, @[email protected]_fragmentation_in_percent_new output, @[email protected]_ Page_space_used_in_percent_new output

--write Log
Insert [dbo]. Indexdefrag values (@DBName, @TableName, @SchemaName, @IndexName, GETDATE (), @avg_fragmentation_in_percent_old, @avg_ Page_space_used_in_percent_old, @avg_fragmentation_in_percent_new, @avg_page_space_used_in_percent_new, ' 1 ')
End
FETCH NEXT from CList to @DBName, @TableName, @SchemaName, @IndexName, @avg_fragmentation_in_percent_old, @avg_page_ Space_used_in_percent_old
End
Close CList
Deallocate cList
END TRY
BEGIN CATCH
SET @EXCEPTION = Error_message ()
END CATCH

IF @EXCEPTION <> '
BEGIN
SET @MailSubject = ' [Important] SDS DB Index maintainence failed from ' + @ @SERVERNAME
EXEC Msdb.dbo.sp_send_dbmail
@profile_name = ' Sdscsql ',
@recipients = '[email protected]‘,
[email protected]_recipients = '[email protected];[email protected]‘,
[email protected]_copy_recipients = ' ',
@body = @EXCEPTION,
@subject = @MailSubject

END

GO

SQL Server Rebuild Index