dbo.QBM_PTableCustomRemove_RU

1  create   procedure QBM_PTableCustomRemove_RU ( @TableName varchar(30) ) as begin declare @UID_DialogTable varchar(38) = null declare @ObjectKeyDialogTable
2 varchar(138) declare @TableType varchar(1) declare @Sammler QBM_YCursorBuffer declare @Step varchar(30) declare @SQLCmd nvarchar(max) declare @ErrorMessage
3 nvarchar(4000) declare @ChildCmd QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY set nocount on select
4 top 1 @UID_DialogTable = t.UID_DialogTable , @ObjectKeyDialogTable = t.XObjectKey , @TableType = t.TableType from DialogTable t where t.TableName = @TableName
5 select @Step = 'Checking ProductionLevel' print @step if exists (select top 1 1 from DialogDatabase db with (readpast) where db.IsMainDatabase = 1 and
6 db.ProductionLevel > 1 ) begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 1, 'QBM_PTableCustomRemove_RU is only allowed in Dev and QA environments'
7 end select @Step = 'Checking QBM-Tables' print @step if @UID_DialogTable is null begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 
81, 'Table not found in DialogTable' end select @Step = 'Checking Table-Type' print @step if @TableType not in ('R', 'U') begin insert into @Sammler (Ident1
9, Bit1, LongIdent1 ) select @Step, 1, 'TableType not in (R, U)' end select @Step = 'Checking schema' print @step if not exists (select top 1 1 from INFORMATION_SCHEMA.TABLES
10 t where t.TABLE_NAME = @TableName ) begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 1, 'Table not found in schema' end if not exists
11 (select top 1 1 from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = @TableName ) begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step
12, 1, 'Table not found in schema' end   select @Step = 'Checking ownership of table' print @step if dbo.QBM_FCVGUIDToModuleOwner(@UID_DialogTable) <> dbo.QBM_FGIDBOwner
13() begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 1, 'You are not the owner of ' + @TableName end if exists (select top 1 1 from 
14@Sammler s where s.Bit1 = 1 ) begin select s.Ident1 as Step , s.Bit1 as IsError , s.LongIdent1 as Remarks , isnull(s.ContentFull, '') as RepairStatement
15 from @Sammler s order by s.ElementIndex select top 1 @ErrorMessage = concat('#LDS#Table will not be dropped. 1st error was {0}.|' , s.LongIdent1 , '|'
16 ) from @sammler s where s.Bit1 = 1 order by s.ElementIndex raiserror(@ErrorMessage, 18, 1) with nowait end else begin print 'try to drop table' select
17 s.Ident1 as Step , s.Bit1 as IsError from @Sammler s order by s.ElementIndex end insert into @ChildCmd(ContentFull) select distinct concat( 'delete ' 
18, r.ChildTable , '
19			from ' , r.ChildTable , ' c 
20			where c.' , r.ChildColumn , ' = ''', @UID_DialogTable, '''
21			' ) from QBM_VQBMRelation r join
22 sys.tables t on r.ChildTable = t.name join sys.columns c on t.object_id = c.object_id and c.name = r.ChildColumn where ParentTable = 'dialogtable' select
23 @ElementCount = @@rowcount begin transaction   delete DialogColumnGroupRight from DialogColumnGroupRight gr join DialogColumn c on gr.UID_DialogColumn
24 = c.UID_DialogColumn where c.UID_DialogTable = @UID_DialogTable delete DialogColumn from DialogColumn c where c.UID_DialogTable = @UID_DialogTable  select
25 @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @SQLCmd = bu.ContentFull from @ChildCmd bu where bu.ElementIndex = @ElementIndex
26 exec sp_executesql @sqlcmd select @ElementIndex += 1 end delete DialogTable from DialogTable a where a.UID_DialogTable = @UID_DialogTable select @SQLCmd
27 = 'drop view ' + @TableName exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , 
28@ProcIDForJournal = default , @HandleErrorSilent = 0 commit Transaction END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default rollback transaction declare
29 @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return end  
30