dbo.QBM_PTableDrop

1   create   procedure QBM_PTableDrop ( @TableName nvarchar(64) , @force bit = 0 ) as begin declare @SQLcmd nvarchar(1024) declare @Message nvarchar
2(1000) declare @uid_DialogTable varchar(38) declare @QBM_VQBMRelationExists bit = 0 declare @QBM_VQBMRelationALLExists bit = 0 declare @DebugSwitch int
3 = 0  declare @ChildCmd QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY exec QBM_PSchemaBindDisable 
4@TableName declare @childtable varchar(64) if exists (select top 1 1 from sys.objects t where t.name = @TableName and t.type <> 'U' ) begin select @Message
5 = '#LDS#Cannot delete Object {0} because it is not found as a table {1}.|' + @TableName + '|' raiserror (@Message, 18, 1) with nowait end if exists (select
6 top 1 1 from sys.objects o where o.name = 'QBM_VQBMRelationALL' ) begin select @QBM_VQBMRelationALLExists = 1 end if exists (select top 1 1 from sys.objects
7 o where o.name = 'QBM_VQBMRelation' ) begin select @QBM_VQBMRelationExists = 1 end if @QBM_VQBMRelationALLExists = 1 begin select @SQLcmd = concat( '
8						select top 1 1
9							from QBM_VQBMRelationALL r
10							where r.ParentTable = '''
11, @TableName , '''
12							and r.ParentTable <> r.ChildTable' ) exec sp_executesql @SQLCmd if @@ROWCOUNT > 0 begin select @Message = '#LDS#Cannot delete Table {0} because it is referenced by table {1}.|'
13 + @TableName + '|' + @childtable + '|' raiserror (@Message, 18, 1) with nowait end end select top 1 @uid_DialogTable = t.UID_DialogTable from DialogTable
14 t where t.TableName = @TableName if @uid_DialogTable is null and @force = 0 begin select @Message = '#LDS#Cannot delete Table {0} because it does not exist in DialogTable.|'
15 + @TableName + '|' raiserror (@Message, 18, 1) with nowait end if dbo.QBM_FCVGUIDToModuleOwner(@uid_DialogTable) <> dbo.QBM_FGIDBOwner() and @force = 
160 begin select @Message = '#LDS#Cannot delete Table {0} because it is not under Your ownership.|' + @TableName + '|' raiserror (@Message, 18, 1) with nowait
17 end exec QBM_PConstraintFKDrop @TableName, '%', '%'   insert into @ChildCmd(ContentFull) select distinct concat( 'delete ' , r.ChildTable , '
18			from '
19 , r.ChildTable , ' c 
20			where c.' , r.ChildColumn , ' = ''', @UID_DialogTable, '''
21			' ) from QBM_VQBMRelation r join sys.tables t on r.ChildTable
22 = t.name join sys.columns c on t.object_id = c.object_id and c.name = r.ChildColumn where ParentTable = 'dialogtable' select @ElementCount = @@rowcount
23 begin transaction if @QBM_VQBMRelationExists = 1 begin select @SQLcmd = '
24		delete QBMRelation 
25		--select r.RelationID
26			from QBMRelation r join  QBM_VQBMRelation v on r.UID_QBMRelation = v.UID_QBMRelation
27			where v.ChildTable = '''
28 + @TableName + '''' exec sp_executesql @SQLcmd end if @QBM_VQBMRelationALLExists = 1 begin select @SQLcmd = '
29			delete DialogValidDynamicRef
30				from DialogValidDynamicRef r join  QBM_VQBMRelationALL v on r.XObjectKey = v.XObjectKey
31				where v.ChildTable = '''
32 + @TableName + '''' exec sp_executesql @SQLcmd end   delete DialogColumnGroupRight from DialogColumnGroupRight gr join DialogColumn c on gr.UID_DialogColumn
33 = c.UID_DialogColumn where c.UID_DialogTable = @UID_DialogTable delete DialogColumn from DialogColumn c where c.UID_DialogTable = @UID_DialogTable  select
34 @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @SQLCmd = bu.ContentFull from @ChildCmd bu where bu.ElementIndex = @ElementIndex
35 exec sp_executesql @sqlcmd select @ElementIndex += 1 end  delete DialogTable where UID_DialogTable = @uid_DialogTable if exists (select top 1 1 from sys.tables
36 t where t.name = @TableName ) begin select @SQLcmd = 'Drop table ' + @TableName if @DebugSwitch > 0 begin print @SQLcmd end exec sp_executesql @SQLcmd
37 end commit transaction END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default rollback transaction declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow
38() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return end 
39