Back to OIM Explorer

dbo.QBM_PModuleRemove

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 12.148 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVStringToList source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PColumnDrop source text reference
  • references source dbo.QBM_PConstraintFKDrop source text reference
  • references source dbo.QBM_PDBQueueActivityDisable source text reference
  • references source dbo.QBM_PFunctionDrop source text reference
  • references source dbo.QBM_PIndexDrop source text reference
  • references source dbo.QBM_PProcedureDrop source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PTriggerDrop source text reference
  • references source dbo.QBM_PTypeDrop source text reference
  • references source dbo.QBM_PViewDrop source text reference
  • references source dbo.QBM_PWatchDogPrepare source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL474 lines
1CREATE PROCEDURE QBM_PModuleRemove(2  @ModuleNamesMVP varchar(2000),3  @FullLog int = 14)5AS6BEGIN7  DECLARE @TableName varchar(30)8  DECLARE @UID_DialogTable varchar(38)9  DECLARE @tableType varchar(1)10  DECLARE @ColumnName varchar(30)11  DECLARE @UID_DialogColumn varchar(38)12  DECLARE @SQLCmd nvarchar(max)13  DECLARE @CountItems int14  DECLARE @DebugSwitch int = 015  DECLARE @ModuleNames QBM_YSingleGUID16  DECLARE @ModuleGUIDClause varchar(2000) = ''17  DECLARE @OneModuleName varchar(10)18  DECLARE @Message nvarchar(1000)19  DECLARE @work TABLE(LineNumber int identity,20  cmd nvarchar(max) collate database_default,21  TableName nvarchar(64) collate database_default,22  Columnname nvarchar(64) collate database_default)23  DECLARE @LineNumber int24  DECLARE @ElementLast int25  DECLARE @ElementBufferMulti QBM_YCursorBuffer26  DECLARE @ElementCount int27  DECLARE @ElementIndex int28  SET XACT_ABORT OFF29  BEGIN TRY30    SET nocount31      ON32    INSERT INTO @ModuleNames(UID_SingleGuid)33    SELECT m.ParameterValue34    FROM dbo.QBM_FCVStringToList(@ModuleNamesMVP,35    char(7),36    1,37    9) m38    IF EXISTS(39      SELECT TOP 1 140      FROM @ModuleNames m41    LEFT42    OUTER43      JOIN QBMModuleDef d44        ON m.UID_SingleGuid = d.ModuleName45      WHERE46        d.UID_ModuleDef IS NULL)47    BEGIN48      RAISERROR('#LDS#Module to delete does not exists.|',49      18,50      1)51        WITH nowait52      RETURN53    END54    IF @FullLog > 055    BEGIN56      SELECT *57      FROM @ModuleNames58    END59    drop TABLE60    IF EXISTS #TableNames61    CREATE TABLE #TableNames(62      TableName varchar(30) collate database_default63    )64    IF NOT EXISTS(65      SELECT TOP 1 166      FROM @ModuleNames67  )68  BEGIN69    GOTO ende70  END71  SELECT @OneModuleName = NULL72  SELECT TOP 1 @OneModuleName = m.UID_SingleGuid73  FROM @ModuleNames m74  JOIN QBMModuleDepend dep75    ON m.UID_SingleGuid =76  LEFT(dep.UID_ModulePredecessor,77  378)79LEFT80OUTER81JOIN @ModuleNames h82  ON h.UID_SingleGuid =83LEFT(dep.UID_ModuleFollower,84385)86WHERE87  h.UID_SingleGuid IS NULL88IF @OneModuleName > ' '89BEGIN90  SELECT91    @Message = 'Can not delete module ' + @OneModuleName + ' because dependend module(s) not in delete list.'92  RAISERROR(@message,93  18,94  195)96  WITH nowait97END98IF EXISTS(99  SELECT TOP 1 1100  FROM sys.triggers tr101  JOIN sys.objects t102    ON tr.parent_id = t.object_id103  JOIN sys.schemas o104    ON t.schema_id = o.schema_id AND o.name = 'dbo'105)106BEGIN107  SELECT108    @Message = 'There are triggers present, use QBM_PTriggerDrop to delete them.'109  RAISERROR(@message,110  18,111  1112)113  WITH nowait114END115IF EXISTS(116  SELECT TOP 1 1117  FROM sys.foreign_keys fk118  JOIN sys.objects c119    ON c.object_id = fk.parent_object_id120  JOIN sys.objects p121    ON p.object_id = fk.referenced_object_id122  JOIN sys.schemas o123    ON c.schema_id = o.schema_id AND o.name = 'dbo'124  JOIN DialogTable dt125    ON c.name = dt.TableName collate database_default126  WHERE127    fk.type = 'F'128)129BEGIN130  SELECT131    @Message = 'There are foreign key constraints present, use QBM_PConstraintFKDrop to delete them.'132  RAISERROR(@message,133  18,134  1135)136  WITH nowait137END138EXEC QBM_PDBQueueActivityDisable139IF EXISTS(140  SELECT TOP 1 1141  FROM sys.sysprocesses p142  WHERE143    p.dbid = DB_ID() AND p.spid <> @@SPID AND p.program_name LIKE CONCAT('OneIM_DatabaseAgent',144'%') AND p.open_tran > 0145)146BEGIN147  SELECT148    @Message = 'There are other processes active, use "exec QBM_PWatchDogPrepare 1" to deactivate them'149  RAISERROR(@message,150  18,151  1152)153  WITH nowait154END155IF @@TRANCOUNT > 0156BEGIN157  SELECT @Message = 'procedure must not be used within transaction(s)'158  RAISERROR(@message,159  18,160  1161)162  WITH nowait163END164DELETE @ElementBufferMulti165INSERT INTO @ElementBufferMulti(UID1166)167SELECT m.UID_SingleGuid168FROM @ModuleNames m169LEFT170JOIN QBMModuleDef md171  ON m.UID_SingleGuid = md.ModuleName172LEFT173JOIN(174SELECT UID_ModulePredecessor175AS176UID_ModuleDef,177count(UID_ModulePredecessor) AS SortOrder178FROM QBMModuleDependCollection179GROUP BY UID_ModulePredecessor) x180  ON md.UID_ModuleDef = x.UID_ModuleDef181ORDER BY isnull(x.SortOrder,1820) ASC183SELECT @ElementCount = @@ROWCOUNT184SELECT @ElementIndex = @@IDENTITY - @ElementCount +1185SELECT @ElementLast = @@IDENTITY186WHILE @ElementIndex <= @ElementLast187BEGIN188  SELECT TOP 1 @OneModuleName = bu.UID1189  FROM @ElementBufferMulti bu190  WHERE191    bu.ElementIndex = @ElementIndex192  IF @ModuleGUIDClause > ' '193  BEGIN194    SELECT195      @ModuleGUIDClause = @ModuleGUIDClause + ', '196  END197  SELECT @ModuleGUIDClause = @ModuleGUIDClause + '''' + @OneModuleName + '-'''198  SELECT @ElementIndex += 1199END200SELECT @ModuleGUIDClause = ' in (' + @ModuleGUIDClause + ')'201IF @DebugSwitch > 0202BEGIN203  print '@ModuleGUIDClause  ' + @ModuleGUIDClause204END205print '-------------------------------------------------' Print '-- drop funktions, procedures, triggers, views ' print '-------------------------------------------------'206DELETE @ElementBufferMulti207INSERT INTO @ElementBufferMulti(UID1)208SELECT m.UID_SingleGuid + '[_]%'209FROM @ModuleNames m210SELECT @ElementCount = @@ROWCOUNT211SELECT @ElementIndex = @@IDENTITY - @ElementCount +1212SELECT @ElementLast = @@IDENTITY213WHILE @ElementIndex <= @ElementLast214BEGIN215  SELECT TOP 1 @OneModuleName = bu.UID1216  FROM @ElementBufferMulti bu217  WHERE218    bu.ElementIndex = @ElementIndex219  IF @FullLog > 0220  BEGIN221    print 'delete code' + @OneModuleName222  END223  EXEC QBM_PFunctionDrop @OneModuleName224  EXEC QBM_PProcedureDrop @OneModuleName225  EXEC QBM_PViewDrop @OneModuleName226  SELECT @ElementIndex += 1227END228SELECT229  @UID_DialogTable = '#' print '---------------------------------------' Print '-- drop tables extended by this module' print '---------------------------------------'230  WHILE @UID_DialogTable > ' '231BEGIN232  SELECT @UID_DialogTable = NULL233  SELECT234    TOP 1 @UID_DialogTable = t.UID_DialogTable,235    @TableName = t.TableName,236    @tableType = t.TableType,237    @OneModuleName = m.UID_SingleGuid238  FROM DialogTable t239  JOIN @ModuleNames m240    ON t.UID_DialogTable LIKE m.UID_SingleGuid + '-%'241  JOIN sys.objects o242    ON t.TableName = o.name243  ORDER BY objectproperty(o.object_id,244  'IsSchemaBound') DESC,245  m.UID_SingleGuid,246  t.TableName247  IF @UID_DialogTable IS NULL248  BEGIN249    CONTINUE250  END251  INSERT INTO #TableNames(TableName)252  SELECT @TableName253  IF @FullLog = 1 print 'table   : ' + @TableName + '  (' + @OneModuleName + ')'254  DELETE DialogTable255  WHERE256    UID_DialogTable = @UID_DialogTable257  DELETE DialogColumn258  WHERE259    UID_DialogTable = @UID_DialogTable260  IF @tableType IN('B',261  'T',262  'M')263  BEGIN264    IF @FullLog = 1 print 'table   : ' + @TableName + '  (' + @OneModuleName + ')'265    SELECT @SQLCmd = char(9) + 'drop table ' + @TableName266    IF @DebugSwitch > 0267    BEGIN268      print @SQLCmd269    END270    IF EXISTS(271      SELECT TOP 1 1272      FROM sys.tables t273      WHERE274        t.name = @TableName)275    BEGIN276      EXEC sp_executeSQL @SQLCmd277    END278  END279  ELSE280  BEGIN281    IF @FullLog = 1 print 'view   : ' + @TableName + '  (' + @OneModuleName + ')'282    EXEC QBM_PViewDrop @TableName,283    1284  END285END286print '---------------------------------------' Print '-- drop columns extended by this module' print '---------------------------------------'287SELECT @UID_DialogColumn = '#'288WHILE @UID_DialogColumn > ' '289BEGIN290  SELECT @UID_DialogColumn = NULL291  SELECT292    TOP 1 @UID_DialogColumn = c.UID_DialogColumn,293    @ColumnName = c.ColumnName,294    @tableType = CASE295    WHEN cc.COLUMN_NAME IS NULL THEN296    'A'297  ELSE t.TableType298  END,299  @TableName = t.TableName,300  @UID_DialogTable = t.UID_DialogTable,301  @OneModuleName = m.UID_SingleGuid302  FROM DialogColumn c303  JOIN DialogTable t304    ON t.UID_DialogTable = c.UID_DialogTable305  JOIN @ModuleNames m306    ON c.UID_DialogColumn LIKE m.UID_SingleGuid + '-%'307  LEFT308  OUTER309  JOIN QBM_VSchemaColumns cc310    ON t.TableName = cc.TABLE_NAME AND c.ColumnName = cc.COLUMN_NAME311  WHERE312    NOT EXISTS(313  SELECT TOP 1 1314  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk315  JOIN sys.key_constraints kc316    ON kc.name = pk.CONSTRAINT_NAME AND kc.type = 'PK'317  WHERE318    pk.TABLE_NAME = cc.TABLE_NAME AND pk.COLUMN_NAME = cc.COLUMN_NAME)319  IF @UID_DialogColumn IS NULL320  BEGIN321    CONTINUE322  END323  IF @FullLog = 1324  BEGIN325    print @TableName + '.' + @columnname + '  (' + @OneModuleName + ')'326  END327  DELETE DialogColumn328  WHERE329    UID_DialogColumn = @UID_DialogColumn330  IF @tableType IN('B',331  'T',332  'M')333  BEGIN334    EXEC QBM_PColumnDrop @TableName,335      @columnname336  END337END338DELETE @ElementBufferMulti339INSERT INTO @ElementBufferMulti(UID1)340SELECT m.UID_SingleGuid + '[_]%'341FROM @ModuleNames m342SELECT @ElementCount = @@ROWCOUNT343SELECT @ElementIndex = @@IDENTITY - @ElementCount +1344SELECT @ElementLast = @@IDENTITY345WHILE @ElementIndex <= @ElementLast346BEGIN347  SELECT TOP 1 @OneModuleName = bu.UID1348  FROM @ElementBufferMulti bu349  WHERE350    bu.ElementIndex = @ElementIndex351  IF @DebugSwitch > 0352  BEGIN353    print 'index delete ' + @OneModuleName354  END355  EXEC QBM_PindexDrop '%',356    @OneModuleName357  EXEC QBM_PindexDrop '%',358  '___[_]XA[1-8]%'359  SELECT @ElementIndex += 1360END361print '---------------------------------------' Print '-- delete content of tables' print '---------------------------------------'362INSERT INTO @work(cmd,363TableName,364Columnname)365SELECT366  ' if exists (select top 1 1 from ' + c.TABLE_NAME + ' where left(' + c.column_name + ', 4) ' + @ModuleGUIDClause + ' )367	' + CASE c.IS_NULLABLE368  WHEN 'YES' THEN369  'update ' + c.TABLE_NAME + ' set ' + c.column_name + ' = null where left(' + c.column_name + ', 4) ' + @ModuleGUIDClause + ' '370ELSE 'delete ' + c.TABLE_NAME + ' where left(' + c.column_name + ', 4) ' + @ModuleGUIDClause + ' '371END AS cmd,372t.TABLE_NAME,373c.COLUMN_NAME374FROM INFORMATION_SCHEMA.COLUMNS c375JOIN INFORMATION_SCHEMA.TABLES t376  ON c.TABLE_NAME = t.TABLE_NAME collate database_default AND t.TABLE_TYPE = 'BASE Table' collate database_default377JOIN sys.tables st378  ON t.TABLE_NAME = st.name collate database_default AND st.is_ms_shipped = 0 AND st.name NOT IN('QBMDBQueueTaskMetric')379WHERE380  c.CHARACTER_MAXIMUM_LENGTH = 38 AND c.DATA_TYPE = 'varchar'381UNION all382SELECT383  ' if exists (select top 1 1 from ' + c.TABLE_NAME + ' d join #TableNames t on d.' + c.column_name + ' like ''<Key><T>'' + t.TableName + ''</T>%'')384	'385  + CASE c.IS_NULLABLE386  WHEN 'YES' THEN387  'update ' + c.TABLE_NAME + ' set ' + c.column_name + ' = null from ' + c.TABLE_NAME + ' d join #TableNames t on d.' + c.column_name + ' like ''<Key><T>'' + t.TableName + ''</T>%'''388  + ' '389ELSE 'delete ' + c.TABLE_NAME + ' from ' + c.TABLE_NAME + ' d join #TableNames t on d.' + c.column_name + ' like ''<Key><T>'' + t.TableName + ''</T>%'''390+ ' '391END AS cmd,392t.TABLE_NAME,393c.COLUMN_NAME394FROM INFORMATION_SCHEMA.COLUMNS c395JOIN INFORMATION_SCHEMA.TABLES t396  ON c.TABLE_NAME = t.TABLE_NAME collate database_default AND t.TABLE_TYPE = 'BASE Table' collate database_default397JOIN sys.tables st398  ON t.TABLE_NAME = st.name collate database_default AND st.is_ms_shipped = 0399WHERE400  c.CHARACTER_MAXIMUM_LENGTH = 138 AND c.DATA_TYPE = 'varchar' AND c.COLUMN_NAME <> 'XObjectKey'401ORDER BY t.TABLE_NAME,402c.COLUMN_NAME403WHILE EXISTS(404SELECT TOP 1 1405FROM @work)406BEGIN407  SELECT408    TOP 1 @LineNumber = w.LineNumber,409    @SQLCmd = w.cmd,410    @TableName = w.TableName,411    @ColumnName = w.Columnname412  FROM @work w413  ORDER BY w.LineNumber414  IF @DebugSwitch > 0415  BEGIN416    print @SQLCmd417  END418  EXEC sp_executesql @SQLCmd419  SELECT @CountItems = @@ROWCOUNT420  IF @CountItems > 0 AND @FullLog = 1421  BEGIN422    print char(9) +@TableName + '.' + + @columnname + ' : ' + str(@CountItems)423  END424  DELETE @work425  WHERE426    LineNumber = @LineNumber427END428print '-------------------------------------------------' Print '-- drop types ' print '-------------------------------------------------'429DELETE @ElementBufferMulti430INSERT INTO @ElementBufferMulti(UID1)431SELECT t.name432FROM sys.types t433JOIN @ModuleNames m434  ON t.name LIKE m.UID_SingleGuid + '[_]Y%'435SELECT @ElementCount = @@ROWCOUNT436SELECT @ElementIndex = @@IDENTITY - @ElementCount +1437SELECT @ElementLast = @@IDENTITY438WHILE @ElementIndex <= @ElementLast439BEGIN440  SELECT TOP 1 @OneModuleName = bu.UID1441  FROM @ElementBufferMulti bu442  WHERE443    bu.ElementIndex = @ElementIndex444  IF @FullLog > 0445  BEGIN446    print 'delete code' + @OneModuleName447  END448  EXEC QBM_PTypeDrop @OneModuleName449  SELECT @ElementIndex += 1450END451DELETE QBMNonLinearDepend452FROM QBMNonLinearDepend de CROSS apply dbo.QBM_FCVStringToList(de.NeededModules,453char(7),4541,4550) sp456JOIN @ModuleNames m457  ON m.UID_SingleGuid = sp.ParameterValue458DELETE QBMModuleDef459FROM QBMModuleDef m460JOIN @ModuleNames n461  ON m.ModuleName = n.UID_SingleGuid drop view462IF EXISTS QBMVSystemOverview truncate TABLE #TableNames463END TRY464BEGIN CATCH465  EXEC QBM_PSessionErrorAdd DEFAULT466  DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()467  RAISERROR(@Rethrow,468  18,469  1)470    WITH NOWAIT471END CATCH472ende:473RETURN474END
Open raw exported source
SQL ยท Raw79 lines
1    create   procedure QBM_PModuleRemove ( @ModuleNamesMVP varchar(2000) , @FullLog int = 1 ) as begin declare @TableName varchar(30) declare @UID_DialogTable2 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 @OneModuleName4 varchar(10) declare @Message nvarchar(1000) declare @work table( LineNumber int identity , cmd nvarchar(max) collate database_default , TableName nvarchar5(64) collate database_default , Columnname nvarchar(64) collate database_default ) declare @LineNumber int declare @ElementLast int declare @ElementBufferMulti6 QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY set nocount on insert into @ModuleNames (UID_SingleGuid7 ) select m.ParameterValue from dbo.QBM_FCVStringToList(@ModuleNamesMVP, char(7), 1,9) m     if exists (select top 1 1 from @ModuleNames m left outer join8 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) with9 nowait return end if @FullLog > 0 begin select * from @ModuleNames end drop table if exists #TableNames create table #TableNames (TableName varchar(3010) collate database_default ) if not exists (select top 1 1 from @ModuleNames ) begin goto ende end select @OneModuleName = null select top 1 @OneModuleName11 = m.UID_SingleGuid  from @ModuleNames m join QBMModuleDepend dep on m.UID_SingleGuid  = left(dep.UID_ModulePredecessor, 3) left outer join @ModuleNames12 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.triggers14 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_id16 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 (@message21, 18, 1) with nowait end delete  @ElementBufferMulti  insert into @ElementBufferMulti (UID1) select m.UID_SingleGuid  from @ModuleNames m left join QBMModuleDef22 md on m.UID_SingleGuid  = md.ModuleName left join (  select UID_ModulePredecessor as UID_ModuleDef, count(UID_ModulePredecessor) as SortOrder from QBMModuleDependCollection23 group by UID_ModulePredecessor )x on md.UID_ModuleDef = x.UID_ModuleDef order by isnull(x.SortOrder, 0) asc select @ElementCount = @@ROWCOUNT select @ElementIndex24 = @@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 (' + @ModuleGUIDClause27 + ')'  if @DebugSwitch > 0 begin print '@ModuleGUIDClause  ' + @ModuleGUIDClause  end print '-------------------------------------------------' Print 28'-- drop funktions, procedures, triggers, views ' print '-------------------------------------------------' delete  @ElementBufferMulti  insert into @ElementBufferMulti29 (UID1) select m.UID_SingleGuid  + '[_]%' from @ModuleNames m select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select30 @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @OneModuleName = bu.UID1 from @ElementBufferMulti bu where bu.ElementIndex31 = @ElementIndex if @FullLog > 0 begin print 'delete code' + @OneModuleName end    exec QBM_PFunctionDrop @OneModuleName exec QBM_PProcedureDrop @OneModuleName32  exec QBM_PViewDrop @OneModuleName   select @ElementIndex += 1 end  select @UID_DialogTable = '#' print '---------------------------------------' Print33 '-- 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 objectproperty36(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_DialogTable38   delete DialogColumn where UID_DialogTable = @UID_DialogTable   if @tableType in ('B', 'T', 'M') begin if @FullLog = 1 print 'table   : ' + @TableName39 + '  (' + @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 select43 top 1 @UID_DialogColumn = c.UID_DialogColumn , @ColumnName = c.ColumnName  , @tableType = case when cc.COLUMN_NAME is null then 'A' else t.TableType end44 , @TableName = t.TableName , @UID_DialogTable = t.UID_DialogTable , @OneModuleName = m.UID_SingleGuid  from DialogColumn c join DialogTable t on t.UID_DialogTable45 = 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_NAME46 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 continue48 end if @FullLog = 1 begin print @TableName + '.' + @columnname + '  (' + @OneModuleName + ')' end delete DialogColumn where UID_DialogColumn = @UID_DialogColumn49   if @tableType in ('B', 'T', 'M') begin exec QBM_PColumnDrop @TableName, @columnname end end  delete  @ElementBufferMulti  insert into @ElementBufferMulti50 (UID1) select m.UID_SingleGuid  + '[_]%' from @ModuleNames m select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select51 @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @OneModuleName = bu.UID1 from @ElementBufferMulti bu where bu.ElementIndex52 = @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) ' + @ModuleGUIDClause55 + ' )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_NAME58 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_name63 + ' = 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 as65 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_default66 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 exists68 (select top 1 1 from @work ) begin select top 1 @LineNumber = w.LineNumber , @SQLCmd = w.cmd , @TableName = w.TableName , @ColumnName = w.Columnname from69 @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 end71  print '-------------------------------------------------' Print '-- drop types ' print '-------------------------------------------------' delete  @ElementBufferMulti72  insert into @ElementBufferMulti (UID1) select t.name from sys.types t join @ModuleNames m on t.name like m.UID_SingleGuid + '[_]Y%' select @ElementCount73 = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top74 1 @OneModuleName = bu.UID1 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex if @FullLog > 0 begin print 'delete code' + @OneModuleName75 end exec QBM_PTypeDrop @OneModuleName select @ElementIndex += 1 end  delete QBMNonLinearDepend  from QBMNonLinearDepend de cross apply dbo.QBM_FCVStringToList76(de.NeededModules, char(7),1 , 0) sp join @ModuleNames m on m.UID_SingleGuid = sp.ParameterValue delete QBMModuleDef from QBMModuleDef m join @ModuleNames77 n on m.ModuleName = n.UID_SingleGuid   drop view if exists QBMVSystemOverview truncate table #TableNames END TRY BEGIN CATCH exec QBM_PSessionErrorAdd78 default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH  ende:  return end 79