dbo.QBM_PModuleRemove

1    create   procedure QBM_PModuleRemove ( @ModuleNamesMVP varchar(2000) , @FullLog int = 1 ) as begin declare @TableName varchar(30) declare @UID_DialogTable
2 varchar(38) declare @tableType varchar(1) declare @ColumnName varchar(30) declare @UID_DialogColumn varchar(38) declare @SQLCmd nvarchar(max) declare 
3@CountItems int declare @DebugSwitch int = 0  declare @ModuleNames QBM_YSingleGUID  declare @ModuleGUIDClause varchar(2000) = '' declare @OneModuleName
4 varchar(10) declare @Message nvarchar(1000) declare @work table( LineNumber int identity , cmd nvarchar(max) collate database_default , TableName nvarchar
5(64) collate database_default , Columnname nvarchar(64) collate database_default ) declare @LineNumber int declare @ElementLast int declare @ElementBufferMulti
6 QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY set nocount on insert into @ModuleNames (UID_SingleGuid
7 ) select m.ParameterValue from dbo.QBM_FCVStringToList(@ModuleNamesMVP, char(7), 1,9) m     if exists (select top 1 1 from @ModuleNames m left outer join
8 QBMModuleDef d on m.UID_SingleGuid = d.ModuleName where d.UID_ModuleDef is null ) begin raiserror('#LDS#Module to delete does not exists.|', 18, 1) with
9 nowait return end if @FullLog > 0 begin select * from @ModuleNames end drop table if exists #TableNames create table #TableNames (TableName varchar(30
10) collate database_default ) if not exists (select top 1 1 from @ModuleNames ) begin goto ende end select @OneModuleName = null select top 1 @OneModuleName
11 = m.UID_SingleGuid  from @ModuleNames m join QBMModuleDepend dep on m.UID_SingleGuid  = left(dep.UID_ModulePredecessor, 3) left outer join @ModuleNames
12 h on h.UID_SingleGuid  = left(dep.UID_ModuleFollower, 3) where h.UID_SingleGuid  is null if @OneModuleName > ' ' begin select @Message = 'Can not delete module '
13 + @OneModuleName + ' because dependend module(s) not in delete list.' raiserror (@message, 18, 1) with nowait end if exists (select top 1 1  from sys.triggers
14 tr join sys.objects t on tr.parent_id = t.object_id join sys.schemas o on t.schema_id = o.schema_id and o.name = 'dbo'  ) begin select @Message = 'There are triggers present, use QBM_PTriggerDrop to delete them.'
15 raiserror (@message, 18, 1) with nowait end if exists (select top 1 1  from sys.foreign_keys fk join sys.objects c on c.object_id = fk.parent_object_id
16 join sys.objects p on p.object_id = fk.referenced_object_id join sys.schemas o on c.schema_id = o.schema_id and o.name = 'dbo' join DialogTable dt on 
17c.name = dt.TableName collate database_default where fk.type = 'F' ) begin select @Message = 'There are foreign key constraints present, use QBM_PConstraintFKDrop to delete them.'
18 raiserror (@message, 18, 1) with nowait end exec QBM_PDBQueueActivityDisable if exists (select top 1 1  from sys.sysprocesses p where p.dbid = DB_ID()
19 and p.spid <> @@SPID and p.program_name like concat( 'OneIM_DatabaseAgent' , '%' ) and p.open_tran > 0 ) begin select @Message = 'There are other processes active, use "exec QBM_PWatchDogPrepare 1" to deactivate them'
20 raiserror (@message, 18, 1) with nowait end if @@TRANCOUNT > 0 begin select @Message = 'procedure must not be used within transaction(s)' raiserror (@message
21, 18, 1) with nowait end delete  @ElementBufferMulti  insert into @ElementBufferMulti (UID1) select m.UID_SingleGuid  from @ModuleNames m left join QBMModuleDef
22 md on m.UID_SingleGuid  = md.ModuleName left join (  select UID_ModulePredecessor as UID_ModuleDef, count(UID_ModulePredecessor) as SortOrder from QBMModuleDependCollection
23 group by UID_ModulePredecessor )x on md.UID_ModuleDef = x.UID_ModuleDef order by isnull(x.SortOrder, 0) asc select @ElementCount = @@ROWCOUNT select @ElementIndex
24 = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @OneModuleName = bu.UID1 from 
25@ElementBufferMulti bu where bu.ElementIndex = @ElementIndex if @ModuleGUIDClause > ' ' begin select @ModuleGUIDClause = @ModuleGUIDClause + ', '  end 
26select @ModuleGUIDClause = @ModuleGUIDClause + '''' + @OneModuleName + '-'''  select @ElementIndex += 1 end  select @ModuleGUIDClause = ' in (' + @ModuleGUIDClause
27 + ')'  if @DebugSwitch > 0 begin print '@ModuleGUIDClause  ' + @ModuleGUIDClause  end print '-------------------------------------------------' Print 
28'-- drop funktions, procedures, triggers, views ' print '-------------------------------------------------' delete  @ElementBufferMulti  insert into @ElementBufferMulti
29 (UID1) select m.UID_SingleGuid  + '[_]%' from @ModuleNames m select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select
30 @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @OneModuleName = bu.UID1 from @ElementBufferMulti bu where bu.ElementIndex
31 = @ElementIndex if @FullLog > 0 begin print 'delete code' + @OneModuleName end    exec QBM_PFunctionDrop @OneModuleName exec QBM_PProcedureDrop @OneModuleName
32  exec QBM_PViewDrop @OneModuleName   select @ElementIndex += 1 end  select @UID_DialogTable = '#' print '---------------------------------------' Print
33 '-- drop tables extended by this module' print '---------------------------------------' while @UID_DialogTable > ' ' begin select @UID_DialogTable = 
34null select top 1 @UID_DialogTable = t.UID_DialogTable , @TableName = t.TableName , @tableType = t.TableType , @OneModuleName = m.UID_SingleGuid  from 
35DialogTable t join @ModuleNames m on t.UID_DialogTable like m.UID_SingleGuid  + '-%' join sys.objects o on t.TableName = o.name order by objectproperty
36(o.object_id, 'IsSchemaBound') desc , m.UID_SingleGuid , t.TableName if @UID_DialogTable is null begin continue end  insert into #TableNames(TableName)
37 select @TableName if @FullLog = 1 print 'table   : ' + @TableName + '  (' + @OneModuleName + ')' delete DialogTable where UID_DialogTable = @UID_DialogTable
38   delete DialogColumn where UID_DialogTable = @UID_DialogTable   if @tableType in ('B', 'T', 'M') begin if @FullLog = 1 print 'table   : ' + @TableName
39 + '  (' + @OneModuleName + ')' select @SQLCmd = char(9) +'drop table ' + @TableName if @DebugSwitch > 0 begin print @SQLCmd end if exists (select top 
401 1 from sys.tables t where t.name = @TableName ) begin exec sp_executeSQL @SQLCmd end end  else begin if @FullLog = 1 print 'view   : ' + @TableName +
41 '  (' + @OneModuleName + ')' exec QBM_PViewDrop @TableName, 1 end end  print '---------------------------------------' Print '-- drop columns extended by this module'
42 print '---------------------------------------' select @UID_DialogColumn = '#' while @UID_DialogColumn > ' ' begin select @UID_DialogColumn = null select
43 top 1 @UID_DialogColumn = c.UID_DialogColumn , @ColumnName = c.ColumnName  , @tableType = case when cc.COLUMN_NAME is null then 'A' else t.TableType end
44 , @TableName = t.TableName , @UID_DialogTable = t.UID_DialogTable , @OneModuleName = m.UID_SingleGuid  from DialogColumn c join DialogTable t on t.UID_DialogTable
45 = c.UID_DialogTable join @ModuleNames m on c.UID_DialogColumn like m.UID_SingleGuid  + '-%'   left outer join QBM_VSchemaColumns cc on t.TableName = cc.TABLE_NAME
46 and c.ColumnName = cc.COLUMN_NAME where Not exists (select top 1 1 from INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk join sys.key_constraints kc on kc.name 
47= pk.CONSTRAINT_NAME and kc.type = 'PK' where pk.TABLE_NAME = cc.TABLE_NAME and pk.COLUMN_NAME = cc.COLUMN_NAME ) if @UID_DialogColumn is null begin continue
48 end if @FullLog = 1 begin print @TableName + '.' + @columnname + '  (' + @OneModuleName + ')' end delete DialogColumn where UID_DialogColumn = @UID_DialogColumn
49   if @tableType in ('B', 'T', 'M') begin exec QBM_PColumnDrop @TableName, @columnname end end  delete  @ElementBufferMulti  insert into @ElementBufferMulti
50 (UID1) select m.UID_SingleGuid  + '[_]%' from @ModuleNames m select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select
51 @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @OneModuleName = bu.UID1 from @ElementBufferMulti bu where bu.ElementIndex
52 = @ElementIndex if @DebugSwitch > 0 begin print 'index delete ' + @OneModuleName end  exec QBM_PindexDrop '%', @OneModuleName exec QBM_PindexDrop '%',
53 '___[_]XA[1-8]%' select @ElementIndex += 1 end  print '---------------------------------------' Print '-- delete content of tables' print '---------------------------------------'
54 insert into @work(cmd, TableName, Columnname) select ' if exists (select top 1 1 from ' + c.TABLE_NAME + ' where left(' + c.column_name + ', 4) ' + @ModuleGUIDClause
55 + ' )
56	' + case c.IS_NULLABLE when 'YES' then 'update ' + c.TABLE_NAME + ' set ' + c.column_name + ' = null where left(' + c.column_name + ', 4) ' + 
57@ModuleGUIDClause + ' ' else 'delete ' + c.TABLE_NAME + ' where left(' + c.column_name + ', 4) ' + @ModuleGUIDClause + ' ' end as cmd , t.TABLE_NAME, c.COLUMN_NAME
58 from INFORMATION_SCHEMA.COLUMNS c join INFORMATION_SCHEMA.TABLES t on c.TABLE_NAME = t.TABLE_NAME collate database_default and t.TABLE_TYPE = 'BASE Table'
59 collate database_default join sys.tables st on t.TABLE_NAME = st.name collate database_default and st.is_ms_shipped = 0 and st.name not in ('QBMDBQueueTaskMetric'
60)  where c.CHARACTER_MAXIMUM_LENGTH = 38 and c.DATA_TYPE = 'varchar' union all select ' if exists (select top 1 1 from ' + c.TABLE_NAME + ' d join #TableNames t on d.'
61 + c.column_name + ' like ''<Key><T>'' + t.TableName + ''</T>%'')
62	' + case c.IS_NULLABLE when 'YES' then 'update ' + c.TABLE_NAME + ' set ' + c.column_name
63 + ' = null from ' + c.TABLE_NAME + ' d join #TableNames t on d.' + c.column_name + ' like ''<Key><T>'' + t.TableName + ''</T>%''' + ' ' else 'delete '
64 + c.TABLE_NAME + ' from ' + c.TABLE_NAME + ' d join #TableNames t on d.' + c.column_name + ' like ''<Key><T>'' + t.TableName + ''</T>%''' + ' ' end as
65 cmd , t.TABLE_NAME, c.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS c join INFORMATION_SCHEMA.TABLES t on c.TABLE_NAME = t.TABLE_NAME collate database_default
66 and t.TABLE_TYPE = 'BASE Table' collate database_default join sys.tables st on t.TABLE_NAME = st.name collate database_default and st.is_ms_shipped = 
670 where c.CHARACTER_MAXIMUM_LENGTH = 138 and c.DATA_TYPE = 'varchar' and c.COLUMN_NAME <> 'XObjectKey' order by t.TABLE_NAME, c.COLUMN_NAME while exists
68 (select top 1 1 from @work ) begin select top 1 @LineNumber = w.LineNumber , @SQLCmd = w.cmd , @TableName = w.TableName , @ColumnName = w.Columnname from
69 @work w order by w.LineNumber if @DebugSwitch > 0 begin print @SQLCmd end exec sp_executesql @SQLCmd select @CountItems = @@ROWCOUNT if @CountItems > 
700 and @FullLog = 1 begin print char(9) +@TableName + '.' + + @columnname + ' : ' + str(@CountItems) end delete  @work where LineNumber = @LineNumber end
71  print '-------------------------------------------------' Print '-- drop types ' print '-------------------------------------------------' delete  @ElementBufferMulti
72  insert into @ElementBufferMulti (UID1) select t.name from sys.types t join @ModuleNames m on t.name like m.UID_SingleGuid + '[_]Y%' select @ElementCount
73 = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top
74 1 @OneModuleName = bu.UID1 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex if @FullLog > 0 begin print 'delete code' + @OneModuleName
75 end exec QBM_PTypeDrop @OneModuleName select @ElementIndex += 1 end  delete QBMNonLinearDepend  from QBMNonLinearDepend de cross apply dbo.QBM_FCVStringToList
76(de.NeededModules, char(7),1 , 0) sp join @ModuleNames m on m.UID_SingleGuid = sp.ParameterValue delete QBMModuleDef from QBMModuleDef m join @ModuleNames
77 n on m.ModuleName = n.UID_SingleGuid   drop view if exists QBMVSystemOverview truncate table #TableNames END TRY BEGIN CATCH exec QBM_PSessionErrorAdd
78 default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH  ende:  return end 
79