Back to OIM Explorer

dbo.QBM_PColumnCustomRemove

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 10.801 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_FCVGUIDToModuleOwner source text reference
  • references source dbo.QBM_FGIDBOwner source text reference
  • references source dbo.QBM_FGITableCountAll source text reference
  • references source dbo.QBM_FSQIndexDef source text reference
  • references source dbo.QBM_FSQIndexDef_i source text reference
  • references source dbo.QBM_PColumnDrop source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PIndexDrop source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PWaitForSeconds source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL573 lines
1CREATE PROCEDURE QBM_PColumnCustomRemove(2  @TableName varchar(30),3  @ColumnName varchar(30),4  @DisplayAllMessages BIT = 05)6AS7BEGIN8  DECLARE @IsTableToDrop BIT = 09  DECLARE @UID_DialogColumn varchar(38) = NULL10  DECLARE @UID_DialogTable varchar(38) = NULL11  DECLARE @ObjectKeyDialogTable varchar(138)12  DECLARE @TableType varchar(1)13  DECLARE @Sammler QBM_YCursorBuffer14  DECLARE @Step varchar(30)15  DECLARE @SQLCmd nvarchar(max)16  DECLARE @ErrorMessage nvarchar(4000)17  DECLARE @ElementCount int18  DECLARE @ElementIndex int19  DECLARE @MessageType varchar(1)20  DECLARE @ChildCmd QBM_YCursorBuffer21  DECLARE @TrancountAtStart int22  DECLARE @ErrorBuffer QBM_YSessionError23  SET XACT_ABORT OFF24  BEGIN TRY25    SET nocount26      ON27    SELECT28      TOP 1 @UID_DialogTable = t.UID_DialogTable,29      @UID_DialogColumn = c.UID_DialogColumn,30      @ObjectKeyDialogTable = t.XObjectKey,31      @TableType = t.TableType32    FROM DialogTable t33    LEFT34    OUTER35    JOIN DialogColumn c36      ON t.UID_DialogTable = c.UID_DialogTable AND c.ColumnName = @ColumnName37    WHERE38      t.TableName = @TableName39    SELECT @Step = 'Checking transaction level'40    IF @DisplayAllMessages = 141    BEGIN42      print @step43    END44    SELECT @TrancountAtStart = @@TRANCOUNT45    IF @TrancountAtStart > 046    BEGIN47      INSERT INTO @Sammler(Ident1,48      Bit1,49      LongIdent1)50      SELECT51        @Step,52        0,53        'QBM_PColumnCustomRemove runs within transaction'54    END55    SELECT @Step = 'Checking ProductionLevel'56    IF @DisplayAllMessages = 157    BEGIN58      print @step59    END60    IF EXISTS(61      SELECT TOP 1 162      FROM DialogDatabase db63      WITH(readpast)64    WHERE65      db.IsMainDatabase = 1 AND db.ProductionLevel > 1)66    BEGIN67      INSERT INTO @Sammler(Ident1,68      Bit1,69      LongIdent1)70      SELECT71        @Step,72        1,73        'QBM_PColumnCustomRemove is only allowed in Dev and QA environments'74    END75    SELECT @Step = 'Checking QBM-Tables'76    IF @DisplayAllMessages = 177    BEGIN78      print @step79    END80    IF @UID_DialogTable IS NULL81    BEGIN82      INSERT INTO @Sammler(Ident1,83      Bit1,84      LongIdent1)85      SELECT86        @Step,87        1,88        'Table not found in DialogTable'89    END90    IF @UID_DialogColumn IS NULL91    BEGIN92      INSERT INTO @Sammler(Ident1,93      Bit1,94      LongIdent1)95      SELECT96        @Step,97        1,98        'Column not found in DialogColumn'99    END100    SELECT @Step = 'Checking Table-Type'101    IF @DisplayAllMessages = 1102    BEGIN103      print @step104    END105    IF @TableType NOT IN('T',106    'B',107    'V')108    BEGIN109      INSERT INTO @Sammler(Ident1,110      Bit1,111      LongIdent1)112      SELECT113        @Step,114        1,115        'TableType not in (B, T, V)'116    END117    SELECT @Step = 'Checking schema'118    IF @DisplayAllMessages = 1119    BEGIN120      print @step121    END122    IF NOT EXISTS(123      SELECT TOP 1 1124      FROM INFORMATION_SCHEMA.TABLES t125      WHERE126        t.TABLE_NAME = @TableName)127    BEGIN128      INSERT INTO @Sammler(Ident1,129      Bit1,130      LongIdent1)131      SELECT132        @Step,133        1,134        'Table not found in schema'135    END136    IF NOT EXISTS(137      SELECT TOP 1 1138      FROM INFORMATION_SCHEMA.COLUMNS c139      WHERE140        c.TABLE_NAME = @TableName AND c.COLUMN_NAME = @ColumnName)141    BEGIN142      INSERT INTO @Sammler(Ident1,143      Bit1,144      LongIdent1)145      SELECT146        @Step,147        1,148        'Column not found in schema'149    END150    SELECT @Step = 'Checking ownership of column'151    IF @DisplayAllMessages = 1152    BEGIN153      print @step154    END155    IF dbo.QBM_FCVGUIDToModuleOwner(@UID_DialogColumn) <> dbo.QBM_FGIDBOwner()156    BEGIN157      INSERT INTO @Sammler(Ident1,158      Bit1,159      LongIdent1)160      SELECT161        @Step,162        1,163        CONCAT('You are not the owner of ',164        @TableName,165        '.',166        @ColumnName)167    END168    SELECT @Step = 'Checking Table to Drop'169    IF @DisplayAllMessages = 1170    BEGIN171      print @step172    END173    IF 0 =(174      SELECT COUNT(*)175    FROM INFORMATION_SCHEMA.COLUMNS c176    WHERE177      c.TABLE_NAME = @tablename AND c.COLUMN_NAME <> @ColumnName)178    BEGIN179      SELECT @IsTableToDrop = 1180      INSERT INTO @Sammler(Ident1,181      Bit1,182      LongIdent1)183      SELECT184        @Step,185        0,186        CONCAT('Last column of table, table will be dropped: ',187        @TableName)188    END189    SELECT @Step = 'Checking PK membership'190    IF @DisplayAllMessages = 1191    BEGIN192      print @step193    END194    IF EXISTS(195      SELECT TOP 1 1196      FROM sys.key_constraints c197      JOIN sys.tables t198        ON c.parent_object_id = t.object_id199      JOIN sys.indexes i200        ON c.name = i.name AND i.is_primary_key = 1 AND i.is_hypothetical = 0 AND i.type NOT IN(3, 4, 5, 6)201    JOIN sys.index_columns ic202      ON ic.object_id = t.object_id AND ic.index_id = i.index_id203    JOIN sys.columns co204      ON t.object_id = co.object_id AND co.column_id = ic.column_id205    WHERE206      t.name = @TableName AND co.name = @ColumnName)207    BEGIN208      INSERT INTO @Sammler(Ident1,209      Bit1,210      LongIdent1)211      SELECT212        @Step,213        0,214        CONCAT('Column is PK-member of table, table will be dropped',215        @TableName)216      SELECT @IsTableToDrop = 1217      SELECT @Step = 'Checking Table empty'218      IF @DisplayAllMessages = 1219      BEGIN220        print @step221      END222      IF dbo.QBM_FGITableCountAll(@tablename) > 0223      BEGIN224        INSERT INTO @Sammler(Ident1,225        Bit1,226        LongIdent1,227        ContentFull)228        SELECT229          @Step,230          1,231          'Table is not empty',232          CONCAT('delete ',233          @tablename)234      END235    END236    IF @IsTableToDrop = 1237    BEGIN238      SELECT @Step = 'Checking ownership of table'239      IF @DisplayAllMessages = 1240      BEGIN241        print @step242      END243      IF dbo.QBM_FCVGUIDToModuleOwner(@UID_DialogTable) <> dbo.QBM_FGIDBOwner()244      BEGIN245        INSERT INTO @Sammler(Ident1,246        Bit1,247        LongIdent1)248        SELECT249          @Step,250          1,251          CONCAT('You are not the owner of table ',252          @TableName)253      END254    END255    SELECT @Step = 'Checking template references'256    IF @DisplayAllMessages = 1257    BEGIN258      print @step259    END260    INSERT INTO @Sammler(Ident1,261    Bit1,262    LongIdent1,263    ContentFull)264    SELECT265      @Step,266      1,267      CONCAT('Column is referenced in template ',268      t.TableName,269      '.',270      cr.ColumnName),271      CONCAT('''',272      cr.Template)273    FROM DialogNotification n274    JOIN DialogColumn cr275      ON n.UID_DialogColumnSubscriber = cr.UID_DialogColumn276    JOIN DialogTable t277      ON cr.UID_DialogTable = t.UID_DialogTable278    WHERE279      n.UID_DialogColumnSender = @UID_DialogColumn AND n.UID_DialogColumnSubscriber <> @UID_DialogColumn280    SELECT @Step = 'Checking referential integrity'281    IF @DisplayAllMessages = 1282    BEGIN283      print @step284    END285    INSERT INTO @Sammler(Ident1,286    Bit1,287    LongIdent1,288    ContentFull)289    SELECT290      @Step,291      1,292      CONCAT('Column is referenced as parent in RI ',293      r.RelationID),294      CONCAT('delete QBMRelation where RelationID = ''',295      r.RelationID,296      '''')297    FROM QBM_VQBMRelation r298    WHERE299      ParentTable = @TableName AND ParentColumn = @ColumnName300    SELECT @Step = 'Checking dynamic RI'301    IF @DisplayAllMessages = 1302    BEGIN303      print @step304    END305    INSERT INTO @Sammler(Ident1,306    Bit1,307    LongIdent1,308    ContentFull)309    SELECT310      @Step,311      1,312      CONCAT('Column is referenced as parent in  ',313      cr.UID_DialogTable,314      '.',315      cr.ColumnName),316      CONCAT('delete DialogValidDynamicRef where UID_DialogColumn = ''',317      r.UID_DialogColumn,318      ''' and UID_DialogTableReference = ''',319      r.UID_DialogTableReference,320      '''')321    FROM DialogValidDynamicRef r322    JOIN DialogTable t323      ON r.UID_DialogTableReference = t.UID_DialogTable AND t.TableName = @TableName324    JOIN DialogColumn c325      ON t.UID_DialogTable = c.UID_DialogTable AND c.ColumnName = @ColumnName AND c.ColumnName = 'XObjectkey'326    JOIN DialogColumn cr327      ON r.UID_DialogColumn = cr.UID_DialogColumn328    SELECT @Step = 'Checking indexes'329    IF @DisplayAllMessages = 1330    BEGIN331      print @step332    END333    INSERT INTO @Sammler(Ident1,334    Bit1,335    LongIdent1,336    ContentFull)337    SELECT338      @Step,339      1,340      CONCAT('Column is contained in Index ',341      t.name,342      '.',343      i.name),344      CONCAT('exec QBM_PIndexDrop ''',345      t.name,346      ''', ''',347      i.name,348      '''349	-- remove ',350      @ColumnName,351      ' in the following line352	',353      dbo.QBM_FSQIndexDef_i(t.name, i.name, 1))354    FROM sys.index_columns ik355      WITH(nolock)356    JOIN sys.columns c357      WITH(nolock)358      ON ik.column_id = c.column_id AND c.name = @columnname AND c.object_id = ik.object_id359    JOIN sys.tables t360      ON t.object_id = ik.object_id AND t.name = @TableName361    JOIN sys.indexes i362      ON ik.index_id = i.index_id AND i.object_id = t.object_id363    WHERE364      i.is_primary_key = 0365    IF EXISTS(366      SELECT TOP 1 1367      FROM @Sammler s368      WHERE369        s.Bit1 = 1)370    BEGIN371      IF @DisplayAllMessages = 1372      BEGIN373        SELECT374          s.Ident1 AS Step,375          s.Bit1 AS IsError,376          s.LongIdent1 AS Remarks,377          isnull(s.ContentFull,378          '') AS RepairStatement379        FROM @Sammler s380        ORDER BY s.ElementIndex381        SELECT TOP 1 @ErrorMessage =382        LEFT(CONCAT('#LDS#Column will not be dropped. 1st error was {0}, repair hint {2} .|', s.LongIdent1, '|',383        s.ContentFull, '|'),384        4000)385        FROM @sammler s386        WHERE387          s.Bit1 = 1388        RAISERROR(@ErrorMessage,389        18,390        1)391          WITH nowait392      END393      ELSE394      BEGIN395        SELECT @ElementCount = count(*)396        FROM @Sammler s397        SELECT @ElementIndex = 1398        WHILE @ElementIndex <= @ElementCount399        BEGIN400          SELECT TOP 1 @ErrorMessage =401          LEFT(CONCAT('#LDS#Column will not be dropped, step {0}, message: {1}, repair hint {2} .|', bu.Ident1,402          '|', bu.LongIdent1, '|', bu.ContentFull, '|'),403          4000),404          @MessageType = CASE bu.Bit1405          WHEN 1 THEN406          'E'407          ELSE 'I'408          END409          FROM @Sammler bu410          WHERE411            bu.ElementIndex = @ElementIndex412          EXEC QBM_PJournal @Errormessage,413            @@procid,414            @MessageType,415          'I'416          EXEC QBM_PWaitForSeconds 0.005417          INSERT INTO @ErrorBuffer(ErrorMessage,418          ErrorSeverity,419          ErrorState,420          ErrorNumber,421          ProcedureName,422          ProcedureLine,423          MessageDate,424          GenProcID,425          RepeatCounter,426          IsReThrow,427          SourceCode)428          SELECT429            @Errormessage,430            18,431            1,432            50000,433            object_name(@@procid),434            0,435            GETUTCDATE(),436            NULL,437            0,438            0,439            NULL440          SELECT @ElementIndex += 1441        END442        SELECT443          TOP 1 @ErrorMessage = CONCAT('#LDS#Column will not be dropped, {0} message(s) logged in journal.|',444          str(@ElementCount),445          '|')446        INSERT INTO @ErrorBuffer(ErrorMessage,447        ErrorSeverity,448        ErrorState,449        ErrorNumber,450        ProcedureName,451        ProcedureLine,452        MessageDate,453        GenProcID,454        RepeatCounter,455        IsReThrow,456        SourceCode)457        SELECT458          @Errormessage,459          18,460          1,461          50000,462          object_name(@@procid),463          0,464          GETUTCDATE(),465          NULL,466          0,467          0,468          NULL469        EXEC QBM_PSessionErrorAdd @Errorbuffer470        RAISERROR(@ErrorMessage,471        18,472        1)473          WITH nowait474      END475    END476    ELSE477    BEGIN478      IF @DisplayAllMessages = 1479      BEGIN480        print 'try to drop column'481      END482      SELECT483        s.Ident1 AS Step,484        s.Bit1 AS IsError485      FROM @Sammler s486      ORDER BY s.ElementIndex487    END488    BEGIN489      TRANSACTION490      IF @IsTableToDrop = 1491      BEGIN492        DELETE DialogColumnGroupRight493        FROM DialogColumnGroupRight gr494        JOIN DialogColumn c495          ON gr.UID_DialogColumn = c.UID_DialogColumn496        WHERE497          c.UID_DialogTable = @UID_DialogTable498        DELETE DialogColumn499        FROM DialogColumn c500        WHERE501          c.UID_DialogTable = @UID_DialogTable502        INSERT INTO @ChildCmd(ContentFull)503        SELECT504          CONCAT('delete ',505          r.ChildTable,506          '507					from ',508          r.ChildTable,509          ' c 510					where c.',511          r.ChildColumn,512          ' = ''',513          @UID_DialogTable,514          '''515					')516        FROM QBM_VQBMRelation r517        WHERE518          ParentTable = 'dialogtable'519        SELECT @ElementCount = @@rowcount520        SELECT @ElementIndex = 1521        WHILE @ElementIndex <= @ElementCount522        BEGIN523          SELECT TOP 1 @SQLCmd = bu.ContentFull524          FROM @ChildCmd bu525          WHERE526            bu.ElementIndex = @ElementIndex527          EXEC sp_executesql @sqlcmd528          SELECT @ElementIndex += 1529        END530        DELETE DialogTable531        FROM DialogTable a532        WHERE533          a.UID_DialogTable = @UID_DialogTable534        SELECT535          @SQLCmd = CONCAT('drop table ',536          @TableName)537        EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd,538          @LockTimeout_ms = DEFAULT,539          @MaxWaitTimeForLock_s = DEFAULT,540          @ProcIDForJournal = DEFAULT,541          @HandleErrorSilent = 0542      END543      ELSE544      BEGIN545        EXEC QBM_PColumnDrop @TableName,546          @ColumnName547      END548      IF @@TRANCOUNT = @TrancountAtStart + 1549      BEGIN550        COMMIT TRANSACTION551      END552      ELSE553      BEGIN554        IF @@TRANCOUNT > 0555        BEGIN556          ROLLBACK TRANSACTION557        END558      END559    END TRY560    BEGIN CATCH561      EXEC QBM_PSessionErrorAdd DEFAULT562      IF @@TRANCOUNT > 0563      BEGIN564        ROLLBACK565      END566      RAISERROR('',567      18,568      1)569        WITH NOWAIT570    END CATCH571    endLabel:572    RETURN573  END
Open raw exported source
SQL ยท Raw75 lines
1    create   procedure QBM_PColumnCustomRemove ( @TableName varchar(30) , @ColumnName varchar(30) , @DisplayAllMessages bit = 0 ) as begin declare2 @IsTableToDrop bit = 0 declare @UID_DialogColumn varchar(38) = null declare @UID_DialogTable varchar(38) = null declare @ObjectKeyDialogTable varchar(1383) declare @TableType varchar(1) declare @Sammler QBM_YCursorBuffer    declare @Step varchar(30) declare @SQLCmd nvarchar(max) declare @ErrorMessage nvarchar4(4000)  declare @ElementCount int declare @ElementIndex int declare @MessageType varchar(1) declare @ChildCmd QBM_YCursorBuffer declare @TrancountAtStart5 int declare @ErrorBuffer QBM_YSessionError SET XACT_ABORT OFF BEGIN TRY set nocount on select top 1 @UID_DialogTable = t.UID_DialogTable , @UID_DialogColumn6 = c.UID_DialogColumn , @ObjectKeyDialogTable = t.XObjectKey , @TableType = t.TableType from DialogTable t left outer join DialogColumn c on t.UID_DialogTable7 = c.UID_DialogTable and c.ColumnName = @ColumnName where t.TableName = @TableName select @Step = 'Checking transaction level' if @DisplayAllMessages =8 1 begin print @step end select @TrancountAtStart = @@TRANCOUNT if @TrancountAtStart > 0 begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select 9@Step, 0, 'QBM_PColumnCustomRemove runs within transaction' end select @Step = 'Checking ProductionLevel' if @DisplayAllMessages = 1 begin print @step 10end if exists (select top 1 1 from DialogDatabase db with (readpast) where db.IsMainDatabase = 1 and db.ProductionLevel > 1 ) begin insert into @Sammler11 (Ident1, Bit1, LongIdent1 ) select @Step, 1, 'QBM_PColumnCustomRemove is only allowed in Dev and QA environments' end select @Step = 'Checking QBM-Tables'12 if @DisplayAllMessages = 1 begin print @step end if @UID_DialogTable is null begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 1, 'Table not found in DialogTable'13 end if @UID_DialogColumn is null begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 1, 'Column not found in DialogColumn' end select14 @Step = 'Checking Table-Type' if @DisplayAllMessages = 1 begin print @step end if @TableType not in ('T', 'B', 'V') begin insert into @Sammler (Ident115, Bit1, LongIdent1 ) select @Step, 1, 'TableType not in (B, T, V)' end select @Step = 'Checking schema' if @DisplayAllMessages = 1 begin print @step end16 if not exists (select top 1 1 from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = @TableName ) begin insert into @Sammler (Ident1, Bit1, LongIdent1 17) select @Step, 1, 'Table not found in schema' end if not exists (select top 1 1 from INFORMATION_SCHEMA.COLUMNS c where c.TABLE_NAME = @TableName and 18c.COLUMN_NAME = @ColumnName ) begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 1, 'Column not found in schema' end   select @Step =19 'Checking ownership of column' if @DisplayAllMessages = 1 begin print @step end if dbo.QBM_FCVGUIDToModuleOwner(@UID_DialogColumn) <> dbo.QBM_FGIDBOwner20() begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 1, concat('You are not the owner of ' , @TableName , '.' , @ColumnName) end select21 @Step = 'Checking Table to Drop' if @DisplayAllMessages = 1 begin print @step end if 0 = (select COUNT(*) from INFORMATION_SCHEMA.COLUMNS c where c.TABLE_NAME22 = @tablename and c.COLUMN_NAME <> @ColumnName ) begin select @IsTableToDrop = 1 insert into @Sammler (Ident1, Bit1, LongIdent1) select @Step, 0, concat23('Last column of table, table will be dropped: ' , @TableName)  end select @Step = 'Checking PK membership' if @DisplayAllMessages = 1 begin print @step24 end if exists (select top 1 1 from sys.key_constraints c join sys.tables t on c.parent_object_id = t.object_id join sys.indexes i on c.name = i.name  25and i.is_primary_key = 1  and i.is_hypothetical = 0  and i.type not in (3,4,5,6) join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id26 = i.index_id join sys.columns co on t.object_id = co.object_id and co.column_id = ic.column_id where t.name = @TableName and co.name = @ColumnName ) begin27 insert into @Sammler (Ident1, Bit1, LongIdent1) select @Step, 0, concat('Column is PK-member of table, table will be dropped' , @TableName)  select @IsTableToDrop28 = 1 select @Step = 'Checking Table empty' if @DisplayAllMessages = 1 begin print @step end if dbo.QBM_FGITableCountAll(@tablename) > 0 begin insert into29 @Sammler (Ident1, Bit1, LongIdent1, ContentFull) select @Step, 1, 'Table is not empty' , concat('delete ' , @tablename) end end if @IsTableToDrop = 1 30begin  select @Step = 'Checking ownership of table' if @DisplayAllMessages = 1 begin print @step end if dbo.QBM_FCVGUIDToModuleOwner(@UID_DialogTable) 31<> dbo.QBM_FGIDBOwner() begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 1, concat('You are not the owner of table ' , @TableName )32 end end select @Step = 'Checking template references' if @DisplayAllMessages = 1 begin print @step end insert into @Sammler (Ident1, Bit1, LongIdent1 33, ContentFull) select @Step, 1, concat('Column is referenced in template ' , t.TableName , '.' , cr.ColumnName) , concat('''' , cr.Template ) from DialogNotification34 n join DialogColumn cr on n.UID_DialogColumnSubscriber = cr.UID_DialogColumn join DialogTable t on cr.UID_DialogTable = t.UID_DialogTable where n.UID_DialogColumnSender35 = @UID_DialogColumn  and n.UID_DialogColumnSubscriber <> @UID_DialogColumn select @Step = 'Checking referential integrity' if @DisplayAllMessages = 1 36begin print @step end insert into @Sammler (Ident1, Bit1, LongIdent1 , ContentFull) select @Step, 1, concat('Column is referenced as parent in RI ' , r.RelationID37 ) , concat('delete QBMRelation where RelationID = ''' , r.RelationID , '''')  from QBM_VQBMRelation r where ParentTable = @TableName and ParentColumn 38= @ColumnName select @Step = 'Checking dynamic RI' if @DisplayAllMessages = 1 begin print @step end insert into @Sammler (Ident1, Bit1, LongIdent1 , ContentFull39) select @Step, 1, concat('Column is referenced as parent in  ' , cr.UID_DialogTable , '.' , cr.ColumnName) , concat('delete DialogValidDynamicRef where UID_DialogColumn = '''40 , r.UID_DialogColumn , ''' and UID_DialogTableReference = ''' , r.UID_DialogTableReference , '''')  from DialogValidDynamicRef r join DialogTable t on41 r.UID_DialogTableReference = t.UID_DialogTable and t.TableName = @TableName join DialogColumn c on t.UID_DialogTable = c.UID_DialogTable and c.ColumnName42 = @ColumnName and c.ColumnName = 'XObjectkey' join DialogColumn cr on r.UID_DialogColumn = cr.UID_DialogColumn  select @Step = 'Checking indexes' if @DisplayAllMessages43 = 1 begin print @step end insert into @Sammler (Ident1, Bit1, LongIdent1 , ContentFull) select @Step, 1, concat('Column is contained in Index ' , t.name44 , '.' , i.name) , concat('exec QBM_PIndexDrop ''' , t.name , ''', ''' , i.name , '''45	-- remove ' , @ColumnName , ' in the following line46	' , dbo.QBM_FSQIndexDef_i47(t.name, i.name, 1) ) from sys.index_columns ik with (nolock) join sys.columns c with (nolock) on ik.column_id = c.column_id and c.name = @columnname and48 c.object_id = ik.object_id join sys.tables t on t.object_id = ik.object_id and t.name = @TableName join sys.indexes i on ik.index_id = i.index_id and 49i.object_id = t.object_id where i.is_primary_key = 0     if exists (select top 1 1 from @Sammler s where s.Bit1 = 1 ) begin if @DisplayAllMessages = 1 50begin select s.Ident1 as Step , s.Bit1 as IsError , s.LongIdent1 as Remarks , isnull(s.ContentFull, '') as RepairStatement from @Sammler s order by s.ElementIndex51 select top 1 @ErrorMessage = left(concat('#LDS#Column will not be dropped. 1st error was {0}, repair hint {2} .|' , s.LongIdent1 , '|' , s.ContentFull52 , '|' ) , 4000) from @sammler s where s.Bit1 = 1 raiserror(@ErrorMessage, 18, 1) with nowait end else begin  select @ElementCount = count(*) from @Sammler53 s select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @ErrorMessage = left(concat('#LDS#Column will not be dropped, step {0}, message: {1}, repair hint {2} .|'54 , bu.Ident1 , '|' , bu.LongIdent1 , '|' , bu.ContentFull , '|' ) , 4000) , @MessageType = case bu.Bit1 when 1 then 'E' else 'I' end from @Sammler bu where55 bu.ElementIndex = @ElementIndex exec QBM_PJournal @Errormessage, @@procid, @MessageType, 'I'  exec QBM_PWaitForSeconds 0.005 insert into @ErrorBuffer 56(ErrorMessage, ErrorSeverity, ErrorState, ErrorNumber , ProcedureName, ProcedureLine, MessageDate , GenProcID, RepeatCounter, IsReThrow, SourceCode) select57 @Errormessage, 18, 1, 50000 , object_name(@@procid), 0, GETUTCDATE() , null, 0, 0, null select @ElementIndex += 1 end  select top 1 @ErrorMessage = concat58('#LDS#Column will not be dropped, {0} message(s) logged in journal.|' , str(@ElementCount) , '|' ) insert into @ErrorBuffer (ErrorMessage, ErrorSeverity59, ErrorState, ErrorNumber , ProcedureName, ProcedureLine, MessageDate , GenProcID, RepeatCounter, IsReThrow, SourceCode) select @Errormessage, 18, 1, 5000060 , object_name(@@procid), 0, GETUTCDATE() , null, 0, 0, null exec QBM_PSessionErrorAdd @Errorbuffer raiserror(@ErrorMessage, 18, 1) with nowait end  end61 else begin if @DisplayAllMessages = 1 begin print 'try to drop column' end select s.Ident1 as Step , s.Bit1 as IsError from @Sammler s order by s.ElementIndex62 end begin transaction if @IsTableToDrop = 1 begin delete DialogColumnGroupRight from DialogColumnGroupRight gr join DialogColumn c on gr.UID_DialogColumn63 = c.UID_DialogColumn where c.UID_DialogTable = @UID_DialogTable delete DialogColumn from DialogColumn c where c.UID_DialogTable = @UID_DialogTable  insert64 into @ChildCmd(ContentFull) select concat( 'delete ' , r.ChildTable , '65					from ' , r.ChildTable , ' c 66					where c.' , r.ChildColumn , ' = ''', 67@UID_DialogTable, '''68					' ) from QBM_VQBMRelation r where ParentTable = 'dialogtable' select @ElementCount = @@rowcount  select @ElementIndex = 1 while69 @ElementIndex <= @ElementCount begin select top 1 @SQLCmd = bu.ContentFull from @ChildCmd bu where bu.ElementIndex = @ElementIndex exec sp_executesql 70@sqlcmd select @ElementIndex += 1 end delete DialogTable from DialogTable a where a.UID_DialogTable = @UID_DialogTable select @SQLCmd = concat('drop table '71 , @TableName) exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal72 = default , @HandleErrorSilent = 0 end else begin exec QBM_PColumnDrop @TableName, @ColumnName end if @@TRANCOUNT = @TrancountAtStart + 1 begin commit73 Transaction end else begin if @@TRANCOUNT > 0 begin rollback transaction end end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default  if @@TRANCOUNT74 > 0 begin rollback end RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: return end  75