Back to OIM Explorer

dbo.QBM_PConstraintChange

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 1.937 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_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL75 lines
1CREATE PROCEDURE QBM_PConstraintChange(2  @ChildTablePattern varchar(30),3  @ConstraintPattern varchar(30),4  @ParentTablePattern varchar(30) = '%',5  @SetDisable BIT = 06)7AS8BEGIN9  DECLARE @TableName nvarchar(64)10  DECLARE @ParentTableName nvarchar(64)11  DECLARE @constraintname nvarchar(64)12  DECLARE @isDisabled BIT13  DECLARE @sqlcmd nvarchar(max)14  DECLARE @elements QBM_YCursorBuffer15  DECLARE @ElementCount int16  DECLARE @ElementIndex int17  SET XACT_ABORT OFF18  BEGIN TRY19    INSERT INTO @elements(LongIdent1,20    Bit1,21    Ident2,22    Ident3)23    SELECT24      DISTINCT fk.name AS ConstraintName,25      fk.is_disabled AS isDisabled,26      c.name AS TableName,27      p.name AS ParentTableName28    FROM sys.foreign_keys fk29    JOIN sys.objects c30      ON c.object_id = fk.parent_object_id31    JOIN sys.objects p32      ON p.object_id = fk.referenced_object_id33    WHERE34      fk.type = 'F' AND fk.name LIKE @Constraintpattern AND c.name LIKE @ChildTablePattern AND p.name LIKE @ParentTablePattern AND(convert(int35    , fk.is_disabled)) ^(convert(int, @SetDisable)) > 036    SELECT @ElementCount = @@ROWCOUNT37    SELECT @ElementIndex = 138    WHILE @ElementIndex <= @ElementCount39    BEGIN40      SELECT41        TOP 1 @constraintname = bu.LongIdent1,42        @isDisabled = bu.Bit1,43        @TableName = bu.Ident2,44        @ParentTableName = bu.Ident345      FROM @elements bu46      WHERE47        bu.ElementIndex = @ElementIndex48      IF @SetDisable = 049      BEGIN50        SELECT51          @sqlcmd = N 'alter table "' + @TableName + N '" with check check constraint ' + @Constraintname52      END53      ELSE54      BEGIN55        SELECT56          @sqlcmd = N 'alter table "' + @TableName + N '" nocheck constraint ' + @Constraintname57      END58      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,59        @LockTimeout_ms = 2500,60        @MaxWaitTimeForLock_s = 10,61        @ProcIDForJournal = DEFAULT,62        @HandleErrorSilent = 0,63        @ExecuteWithTransact = 064      SELECT @ElementIndex += 165    END66  END TRY67  BEGIN CATCH68    EXEC QBM_PSessionErrorAdd DEFAULT69    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()70    RAISERROR(@Rethrow,71    18,72    1)73      WITH NOWAIT74  END CATCH75END
Open raw exported source
SQL ยท Raw14 lines
1     create   procedure QBM_PConstraintChange (@ChildTablePattern varchar(30), @ConstraintPattern varchar(30), @ParentTablePattern varchar(30) =2 '%', @SetDisable bit = 0 ) as begin declare @TableName nvarchar(64) declare @ParentTableName nvarchar(64) declare @constraintname nvarchar(64) declare3 @isDisabled bit declare @sqlcmd nvarchar(max) declare @elements QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int      SET XACT_ABORT4 OFF BEGIN TRY insert into @elements(LongIdent1 , Bit1 , Ident2 , Ident3 ) select distinct fk.name as ConstraintName , fk.is_disabled as isDisabled, c.name5 as TableName, p.name as ParentTableName  from sys.foreign_keys fk join sys.objects c on c.object_id = fk.parent_object_id join sys.objects p on p.object_id6 = fk.referenced_object_id where fk.type = 'F' and fk.name like @Constraintpattern and c.name like @ChildTablePattern and p.name like @ParentTablePattern7 and (convert(int, fk.is_disabled)) ^ ( convert(int, @SetDisable)) > 0 select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <=8 @ElementCount begin select top 1 @constraintname = bu.LongIdent1  , @isDisabled = bu.Bit1  , @TableName = bu.Ident2  , @ParentTableName = bu.Ident3  from9 @elements bu where bu.ElementIndex = @ElementIndex if @SetDisable = 0 begin select @sqlcmd = N'alter table "' + @TableName + N'" with check check constraint '10 + @Constraintname end else begin select @sqlcmd = N'alter table "' + @TableName + N'" nocheck constraint ' + @Constraintname end  exec QBM_PExecuteSQLWithRetry_LLP11 @SQLStatement = @SQLcmd , @LockTimeout_ms = 2500  , @MaxWaitTimeForLock_s = 10 , @ProcIDForJournal = default , @HandleErrorSilent = 0   , @ExecuteWithTransact12 = 0 select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow13() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH end 14