dbo.QBM_PTableReload

1       create   procedure QBM_PTableReload (@TableName varchar(38) , @WithReplace bit  , @Genprocid varchar(38) = null , @DiskStoreMove bit = 0 
2 ) as begin declare @TableName_old varchar(30) = left('T'+ replace(newid(), '-', ''), 30) declare @SQLCmd nvarchar(max) declare @CreateCmd nvarchar(max
3) declare @InsertCmd nvarchar(max) declare @IndexesAndTriggers QBM_YCursorBuffer     declare @CodeType char(1) declare @CodeName varchar(30) declare @i
4 int declare @CountIndexesAndTriggers int declare @DebugSwitch int = 0 declare @DebugLevel char(1) = 'W' declare @Debugmessage nvarchar(1000) declare @Productionlevel
5 int SET XACT_ABORT OFF BEGIN TRY if @Genprocid is null begin select @Genprocid = NEWID() end if exists (select top 1 1 from sys.views v where v.name =
6 'QBM_VTablesReloadAble' ) begin if not exists (select top 1 1 from QBM_VTablesReloadAble v where v.TableName = @TableName ) begin goto endLabel end end
7 if  @DiskStoreMove = 0 begin if @TableName in ( select v.TableName from QBM_VHeavyLoadTables v where v.IsNoReload = 1 ) begin goto endLabel end end  select
8 top 1 @Productionlevel = db.ProductionLevel from DialogDatabase db with (readpast) where db.IsMainDatabase = 1 if @Productionlevel > 1 and @WithReplace
9 = 1  begin raiserror('#LDS#Replacement is not permitted in production environment|', 18, 1) with nowait goto EndLabel end if @DiskStoreMove = 0 and dbo.QBM_FGIDiskStoreExisting
10(@TableName) <> dbo.QBM_FGIDiskStoreTarget(@TableName) begin goto endlabel end if @DiskStoreMove = 1 and @TableName in (select TableName from QBM_VStartupTables
11 union select 'QBMDiskStoreLogical' union select 'QBMDiskStorePhysical' ) begin  goto endLabel end begin transaction  insert into @IndexesAndTriggers(ContentFull
12,Ident1 , Ident2 ) select dbo.QBM_FSQIndexDef_i (t.name, i.name, 1), 'I', left(i.name, 30) from sys.tables t join sys.indexes i on t.object_id = i.object_id
13 where t.name = @TableName and i.is_primary_key = 0 and i.is_hypothetical = 0  and i.type not in (3,4,5,6) select @CountIndexesAndTriggers = @@ROWCOUNT
14    insert into @IndexesAndTriggers(ContentFull, Ident1 , Ident2 ) select m.definition, 'T', left(tr.name, 30) from sys.tables t join sys.triggers tr on
15 t.object_id = tr.parent_id join sys.sql_modules m on m.object_id = tr.object_id where t.name = @TableName  and tr.name not like 'GEN[_]%'  and tr.name
16 != 'QBM_TUIndicator474488' select @CountIndexesAndTriggers += @@ROWCOUNT  insert into @IndexesAndTriggers(ContentFull, Ident1 , Ident2 ) select f.SourceCode
17, f.ObjectType, f.ObjectName from dbo.QBM_FTSchemaDependencies(@TableName, 1) f where f.ObjectType in ('F', 'V') select @CountIndexesAndTriggers += @@ROWCOUNT
18 select @i = 1 while @i <= @CountIndexesAndTriggers begin select top 1 @CodeType = i.Ident1  , @CodeName = i.Ident2  from @IndexesAndTriggers i where i.ElementIndex
19 = @i if @CodeType = 'F' begin if @DebugSwitch > 0 begin print 'dropping funct ' + @CodeName end  exec QBM_PFunctionDrop @CodeName end if @CodeType = 'V'
20 begin if @DebugSwitch > 0 begin print 'dropping view  ' + @CodeName end exec QBM_PViewDrop @CodeName end select @i += 1 end    exec QBM_PTriggerDrop '%'
21, @TableName , @CustomTriggerAlso = 1 exec QBM_PConstraintFKDrop @TableName , '%', '%' exec QBM_PConstraintFKDrop '%', '%', @TableName  select @CreateCmd
22 = dbo.QBM_FSQTableDef(@TableName ) select @InsertCmd = 'Insert into ' + @TableName + '
23				(
24		' + dbo.QBM_FCVStringToIndent( dbo.QBM_FSQColumnList(@TableName
25 , 0, 1), 4) + '
26				) 
27		select 
28		' + dbo.QBM_FCVStringToIndent( dbo.QBM_FSQColumnList(@TableName , @WithReplace, 1), 4) + '
29		from ' + @TableName_old
30 if @DebugSwitch > 0 begin print 'TableName ' + isnull(@TableName, '#') print '@TableName_old ' + isnull(@TableName_old, '#') end  exec sp_rename @TableName
31 , @TableName_old, 'object' if @DebugSwitch > 0 begin print @CreateCmd end exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @CreateCmd , @LockTimeout_ms
32 = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@PROCID , @HandleErrorSilent = 0  if @DebugSwitch > 0 begin print @Insertcmd end exec
33 QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @insertcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@PROCID ,
34 @HandleErrorSilent = 0 if @DebugSwitch > 0 begin select i.ElementIndex, i.Ident2  from @IndexesAndTriggers i end  select @i = 1 while @i <= @CountIndexesAndTriggers
35 begin select top 1 @SQLCmd = i.ContentFull , @CodeType = i.Ident1  , @CodeName = i.Ident2  from @IndexesAndTriggers i where i.ElementIndex = @i if @DebugSwitch
36 > 0 begin print @CodeType + ' ' + @Codename end if @CodeType = 'T' begin   exec sp_executesql @sqlcmd end if @CodeType = 'I' begin exec sp_executesql 
37@sqlcmd end if @CodeType = 'F' begin exec sp_executesql @sqlcmd end if @CodeType = 'V' begin exec sp_executesql @sqlcmd end select @i += 1 end   select
38 @SQLCmd = 'drop table ' + @TableName_old if @DebugSwitch > 0 begin print @SQLCmd end exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd , @LockTimeout_ms
39 = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@PROCID , @HandleErrorSilent = 0 select @Debugmessage = 'reload table ' + @TableName
40 exec QBM_PJournal @debugmessage, @@procid, 'D', @DebugLevel commit transaction  exec QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate' , 'QBM-K-MakeConstraint'
41, '', @Genprocid exec QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate' , 'QBM-K-CommonMakeRITrigger', '', @Genprocid exec QBM_PDBQueueInsert_Single 
42'QBM-K-CommonMakeWatchtrigger', @TableName , '', @Genprocid exec QBM_PDBQueueInsert_Single 'QBM-K-SetRowLockOnly', '', '', @Genprocid exec QBM_PDBQueueInsert_Single
43 'QBM-K-CommonReCalculate' , 'QBM-K-CommonRebuildView', '', @Genprocid END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default rollback transaction RAISERROR
44 ('', 18, 1) WITH NOWAIT END CATCH endLabel: return end 
45