Back to OIM Explorer

dbo.QBM_PConstraintFKDrop

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 5.379 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_FCVIntToString source text reference
  • references source dbo.QBM_FGIObjectIsDropable source text reference
  • references source dbo.QBM_FGISessionErrorIsDeadlock source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PSessionErrorClean source text reference

References

Referenced By

Complete Source

SQL217 lines
1CREATE PROCEDURE QBM_PConstraintFKDrop(2  @tablepattern nvarchar(64),3  @ConstraintPattern nvarchar(64),4  @ParentTablePattern nvarchar(64) = '%'5)6AS7BEGIN8  DECLARE @TableName varchar(64)9  DECLARE @constraintname varchar(64)10  DECLARE @ownername varchar(64)11  DECLARE @sqlcmd nvarchar(max)12  DECLARE @lauf int = 113  DECLARE @ErrorMessage nvarchar(4000)14  DECLARE @ErrorSeverity int15  DECLARE @ErrorState int16  DECLARE @LockRetry BIT17  DECLARE @DebugLevel char(1) = 'W'18  DECLARE @DebugSwitch int = 019  DECLARE @MessageType varchar(1) = 'W'20  DECLARE @elementsToDrop TABLE(TableName varchar(64) collate database_default,21  ConstraintName varchar(64) collate database_default,22  OwnerName varchar(64) collate database_default)23  DECLARE @ElementsAffected int = 024  DECLARE @JournalMessage nvarchar(1000)25  DECLARE @JournalProcedureExists BIT = 026  DECLARE @CountElementsChanged int = 027  DECLARE @FehlerAufgetreten BIT = 028  SET XACT_ABORT OFF29  BEGIN TRY30    IF EXISTS(31      SELECT TOP 1 132      FROM sys.objects o33      WHERE34        o.name = 'QBM_PJournal' AND type = 'P')35    BEGIN36      SELECT @JournalProcedureExists = 137    END38    IF EXISTS(39      SELECT TOP 1 140      FROM sys.tables t41      WHERE42        t.name = 'DialogTable' collate database_default)43    BEGIN44      SELECT45        @SQLcmd = '46			select c.name, fk.name, o.name47			from sys.foreign_keys fk join sys.objects c on c.object_id = fk.parent_object_id48									join sys.objects p on p.object_id = fk.referenced_object_id49									join sys.schemas o on c.schema_id = o.schema_id50														and dbo.QBM_FGIObjectIsDropable (o.schema_id)=151									join DialogTable dt with (readpast) on c.name = dt.TableName  collate database_default52			where fk.type = ''F''53				and fk.name like '''54        + @Constraintpattern + '''  collate database_default55				and c.name like ''' + @Tablepattern+ '''  collate database_default56				and p.name like '''57        + @ParentTablePattern+ '''  collate database_default58			'59    END60    ELSE61    BEGIN62      SELECT63        @SQLcmd = '64			select c.name, fk.name, o.name65			from sys.foreign_keys fk join sys.objects c on c.object_id = fk.parent_object_id66									join sys.objects p on p.object_id = fk.referenced_object_id67									join sys.schemas o on c.schema_id = o.schema_id68														and dbo.QBM_FGIObjectIsDropable (o.schema_id)=169													and o.name = ''dbo''  collate database_default70			where fk.type = ''F''  collate database_default71				and fk.name like '''72        + @Constraintpattern + '''  collate database_default73				and c.name like ''' + @Tablepattern+ '''  collate database_default74				and p.name like '''75        + @ParentTablePattern+ '''  collate database_default76			'77    END78    IF @DebugSwitch > 079    BEGIN80      print @sqlcmd81    END82    INSERT INTO @elementsToDrop(TableName,83    ConstraintName,84    OwnerName)85    EXEC sp_executesql @sqlcmd86    SELECT @ElementsAffected = @@ROWCOUNT87    SELECT @LockRetry = 588    WHILE @LockRetry > 089    BEGIN90      SELECT @lauf = 191      SELECT @constraintname = '#'92      WHILE @constraintname > ' ' AND @lauf > 093      BEGIN94        SELECT @constraintname = NULL95        SELECT96          TOP 1 @constraintname = td.ConstraintName,97          @ownerName = td.OwnerName,98          @TableName = td.TableName99        FROM @elementsToDrop td100        IF @constraintname IS NULL101        BEGIN102          SELECT @lauf = 0103          SELECT @LockRetry = 0 CONTINUE104        END105        SELECT106          @sqlcmd = CONCAT(N 'alter table ',107          @ownername,108          '.',109          @TableName,110          N ' drop constraint ',111          ' if exists ',112          @Constraintname,113          nchar(13),114          nchar(10))115        IF @JournalProcedureExists = 1116        BEGIN117          EXEC QBM_PJournal @SQLcmd,118            @@PROCID,119          'D',120            @DebugLevel121        END122        IF @DebugSwitch > 0123        BEGIN124          print @sqlcmd125        END126        BEGIN TRY127          EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,128            @LockTimeout_ms = 2500,129            @MaxWaitTimeForLock_s = 15.0,130            @ProcIDForJournal = DEFAULT,131            @HandleErrorSilent = 0,132            @DeadlockPriority = 5,133            @ExecuteWithTransact = 0134          DELETE @elementsToDrop135          FROM @elementsToDrop td136          WHERE137            td.TableName = @TableName AND td.ConstraintName = @constraintname AND td.OwnerName = @ownerName138          SELECT @CountElementsChanged += 1139          EXEC QBM_PSessionErrorClean140        END TRY141        BEGIN CATCH142          EXEC QBM_PSessionErrorAdd DEFAULT,143            @sqlcmd144          IF dbo.QBM_FGISessionErrorIsDeadlock(DEFAULT) = 1145          BEGIN146            SELECT @LockRetry -= 1147            IF @JournalProcedureExists = 1148            BEGIN149              EXEC QBM_PJournal 'SessionError',150                @@PROCID,151              'W',152                @DebugLevel153            END154            SELECT @Errormessage = NULL waitfor delay '00:00:00.500'155          END156          ELSE157          BEGIN158            SELECT @FehlerAufgetreten = 1159            IF @JournalProcedureExists = 1160            BEGIN161              EXEC QBM_PJournal 'SessionError',162                @@PROCID,163              'E',164                @DebugLevel165            END166            SELECT @lauf = 0167            SELECT @LockRetry = 0168          END169        END CATCH170      END171    END172    IF @CountElementsChanged > 0 AND @JournalProcedureExists = 1173    BEGIN174      SELECT175        @JournalMessage = '#LDS#System elements modified by {0}, pattern {1}, elements affected {2}.|' + isnull(OBJECT_NAME(@@procid),176        '') + '|' + @tablepattern + '|' + dbo.QBM_FCVIntToString(@CountElementsChanged) + '|'177      IF NOT EXISTS(178        SELECT TOP 1 1179        FROM DialogDatabase180        WHERE181          IsMainDatabase = 1 AND UpdatePhase <> 0) AND NOT EXISTS(182      SELECT TOP 1 1183      FROM sys.tables t184        WITH(readpast)185      JOIN sys.columns c186        WITH(readpast)187        ON t.object_id = c.object_id188      WHERE189        t.name = 'DialogDatabase' AND c.name = 'InitialMigrationRunning')190      BEGIN191        EXEC QBM_PJournal @JournalMessage,192          @@procid,193          @MessageType,194          @DebugLevel,195        20196      END197      ELSE198      BEGIN199        EXEC QBM_PJournal @JournalMessage,200          @@procid,201        'I',202        'I'203      END204    END205  END TRY206  BEGIN CATCH207    EXEC QBM_PSessionErrorAdd DEFAULT208  END CATCH209  IF @FehlerAufgetreten = 1210  BEGIN211    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()212    RAISERROR(@Rethrow,213    18,214    1)215      WITH NOWAIT216  END217END
Open raw exported source
SQL ยท Raw53 lines
1    create   procedure QBM_PConstraintFKDrop (@tablepattern nvarchar(64), @ConstraintPattern nvarchar(64), @ParentTablePattern nvarchar(64) = '%'2 ) as begin declare @TableName varchar(64)  declare @constraintname varchar(64) declare @ownername varchar(64) declare @sqlcmd nvarchar(max) declare @lauf3 int = 1 declare @ErrorMessage nvarchar(4000) declare @ErrorSeverity int declare @ErrorState int declare @LockRetry bit declare @DebugLevel char(1) = 'W'4 declare @DebugSwitch int = 0 declare @MessageType varchar(1) = 'W' declare @elementsToDrop table (TableName varchar(64) collate database_default , ConstraintName5 varchar(64) collate database_default , OwnerName varchar(64) collate database_default ) declare @ElementsAffected int = 0 declare @JournalMessage nvarchar6(1000) declare @JournalProcedureExists bit = 0 declare @CountElementsChanged int = 0 declare @FehlerAufgetreten bit = 0 SET XACT_ABORT OFF BEGIN TRY if7 exists (select top 1 1 from sys.objects o where o.name = 'QBM_PJournal' and type = 'P' ) begin select @JournalProcedureExists = 1 end  if exists (select8 top 1 1 from sys.tables t where t.name = 'DialogTable' collate database_default ) begin  select @SQLcmd = '9			select c.name, fk.name, o.name10			from sys.foreign_keys fk join sys.objects c on c.object_id = fk.parent_object_id11									join sys.objects p on p.object_id = fk.referenced_object_id12									join sys.schemas o on c.schema_id = o.schema_id13														and dbo.QBM_FGIObjectIsDropable (o.schema_id)=114									join DialogTable dt with (readpast) on c.name = dt.TableName  collate database_default15			where fk.type = ''F''16				and fk.name like '''17 + @Constraintpattern + '''  collate database_default18				and c.name like ''' + @Tablepattern+ '''  collate database_default19				and p.name like ''' 20+ @ParentTablePattern+ '''  collate database_default21			' end else begin  select @SQLcmd = '22			select c.name, fk.name, o.name23			from sys.foreign_keys fk join sys.objects c on c.object_id = fk.parent_object_id24									join sys.objects p on p.object_id = fk.referenced_object_id25									join sys.schemas o on c.schema_id = o.schema_id26														and dbo.QBM_FGIObjectIsDropable (o.schema_id)=127													and o.name = ''dbo''  collate database_default28			where fk.type = ''F''  collate database_default29				and fk.name like '''30 + @Constraintpattern + '''  collate database_default31				and c.name like ''' + @Tablepattern+ '''  collate database_default32				and p.name like ''' 33+ @ParentTablePattern+ '''  collate database_default34			' end if @DebugSwitch > 0 begin print @sqlcmd end insert into @elementsToDrop(TableName, ConstraintName35, OwnerName) exec sp_executesql @sqlcmd select @ElementsAffected = @@ROWCOUNT select @LockRetry = 5 while @LockRetry > 0 begin  select @lauf = 1 select36 @constraintname = '#' while @constraintname > ' ' and @lauf > 0 begin select @constraintname = null select top 1 @constraintname = td.ConstraintName ,37 @ownerName = td.OwnerName , @TableName = td.TableName from @elementsToDrop td if @constraintname is null begin select @lauf = 0 select @LockRetry = 0 38continue end select @sqlcmd = concat( N'alter table ' , @ownername , '.' , @TableName , N' drop constraint '  , ' if exists ' , @Constraintname , nchar39(13) , nchar(10) ) if @JournalProcedureExists = 1 begin exec QBM_PJournal @SQLcmd, @@PROCID, 'D', @DebugLevel end if @DebugSwitch > 0 begin print @sqlcmd40 end BEGIN TRY  exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = 2500 , @MaxWaitTimeForLock_s = 15.0 , @ProcIDForJournal =41 default , @HandleErrorSilent = 0 , @DeadlockPriority = 5  , @ExecuteWithTransact = 0 delete  @elementsToDrop from @elementsToDrop td where td.TableName42 = @TableName and td.ConstraintName = @constraintname and td.OwnerName = @ownerName select @CountElementsChanged += 1 exec QBM_PSessionErrorClean END TRY43 BEGIN CATCH exec QBM_PSessionErrorAdd default, @sqlcmd if dbo.QBM_FGISessionErrorIsDeadlock(default) = 1 begin select @LockRetry -= 1 if @JournalProcedureExists44 = 1 begin exec QBM_PJournal 'SessionError', @@PROCID, 'W', @DebugLevel end select @Errormessage = null waitfor delay '00:00:00.500' end else begin select45 @FehlerAufgetreten = 1 if @JournalProcedureExists = 1 begin exec QBM_PJournal 'SessionError', @@PROCID, 'E', @DebugLevel end select @lauf = 0 select @LockRetry46 = 0 end END CATCH end  end  if @CountElementsChanged > 0 and @JournalProcedureExists = 1 begin select @JournalMessage = '#LDS#System elements modified by {0}, pattern {1}, elements affected {2}.|'47 + isnull(OBJECT_NAME(@@procid), '') + '|' + @tablepattern + '|' + dbo.QBM_FCVIntToString(@CountElementsChanged) + '|'  if not exists (select top 1 1 from48 DialogDatabase where IsMainDatabase = 1 and UpdatePhase <> 0) and not exists (select top 1 1 from sys.tables t with (readpast) join sys.columns c with49 (readpast) on t.object_id = c.object_id where t.name = 'DialogDatabase' and c.name = 'InitialMigrationRunning' ) begin exec QBM_PJournal @JournalMessage50, @@procid, @MessageType, @DebugLevel, 20 end else begin  exec QBM_PJournal @JournalMessage, @@procid, 'I', 'I' end end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd51 default END CATCH if @FehlerAufgetreten = 1 begin declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT52 end end 53