dbo.QBM_PIndexRebuild

1   create   procedure QBM_PIndexRebuild ( @TableName varchar(30) ) as begin declare @SQLcmd nvarchar(max) declare @tableID int declare @SQLcmds 
2QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DebugLevel char(1) = 'W' declare @GenProcID varchar(38) = dbo.QBM_FGISessionContext
3('') declare @RebuildOptions nvarchar(max) declare @ErrorBuffer QBM_YSessionError SET XACT_ABORT OFF BEGIN TRY if @@TRANCOUNT > 0 begin  goto endlabel 
4end if isnull(@TableName, '') = '' begin goto ende end select @tableID = null select top 1 @tableID = OBJECT_ID from sys.tables where name = @TableName
5 exec QBM_PTableLockEscalationSet @TableName  if @TableName in (select TableName from QBM_VHeavyLoadTables  ) begin goto ende end  if not exists (select
6 top 1 1 from information_schema.tables where table_name = @TableName and table_type = 'BASE TABLE' ) or @tableID is null begin select @SQLcmd = N'No reorganize for Table '
7 + @TableName + N' Table not exists' exec QBM_PJournal @SQLcmd, @@procid, 'W', @DebugLevel goto ende end  exec QBM_PGIDBServerIndexRebuildOpt @TableName
8, @RebuildOptions output  set lock_timeout 5000 select @ElementCount = 0 BEGIN TRY insert into @SQLcmds (ContentFull) SELECT N'ALTER INDEX "' + x.indexname
9 + N'" ON ' + schemaname + N'.' + @TableName + case when x.PercentFrag < 20.0 and x.index_id > 1 then N' REORGANIZE'   else @RebuildOptions end from dbo.QBM_FTIndexFragmentation
10(@TableName, 5) x order by x.index_id select @ElementCount = @@rowcount END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default insert into @ErrorBuffer 
11(ErrorMessage, ErrorSeverity, ErrorState, ErrorNumber , ProcedureName, ProcedureLine, MessageDate , GenProcID, RepeatCounter, IsReThrow, SourceCode) select
12 ERROR_MESSAGE(),ERROR_SEVERITY(), ERROR_STATE(), 50000 , ERROR_PROCEDURE() , ERROR_LINE(), GETUTCDATE() , null, 0, 0, 'select fr.object_id, fr.index_id ...FROM sys.dm_db_index_physical_stats ...'
13 exec QBM_PSessionErrorAdd @ErrorBuffer raiserror ('', 18, 1) END CATCH if @ElementCount > 0 begin  select @SQLCmd = N'ALTER INDEX ALL ON ' + @TableName
14 + N' SET (ALLOW_PAGE_LOCKS = ON)' exec QBM_PJournal @SQLcmd, @@PROCID , 'D', @DebugLevel exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms
15 = default  , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 , @ExecuteWithTransact = 0 select @ElementIndex =
16 1 while @ElementIndex <= @ElementCount begin select top 1 @SQLcmd = bu.ContentFull from @SQLcmds bu where bu.ElementIndex = @ElementIndex exec QBM_PJournal
17 @sqlcmd, @@PROCID, 'D', @DebugLevel exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = 10.0
18  , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 , @ExecuteWithTransact = 0 select @ElementIndex += 1 end  end  exec QBM_PSetRowLockOnly @TableName
19 if not exists (select top 1 1 from DialogDBQueue q with (readpast) where q.UID_Task = 'QBM-K-UpdateStatistics' ) begin exec QBM_PDBQueueInsert_Single 
20'QBM-K-UpdateStatistics', '', '', @GenProcID end ende: return END TRY BEGIN CATCH  exec QBM_PSetRowLockOnly @TableName exec QBM_PSessionErrorAdd default
21 raiserror ('', 18, 1) END CATCH endLabel: return end 
22