Back to OIM Explorer

dbo.QBM_PMakeConstraint

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.796 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_FGIObjectIsDropable source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_FSQFKCheckFix source text reference
  • references source dbo.QBM_PConstraintFKDrop source text reference
  • references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PSessionErrorClean source text reference

References

Referenced By

Complete Source

SQL166 lines
1CREATE PROCEDURE QBM_PMakeConstraint(2  @Table nvarchar(32)3)4AS5BEGIN6  DECLARE @ConstraintDefs QBM_YCursorBuffer7  DECLARE @ElementCount int8  DECLARE @ElementIndex int9  DECLARE @ElementLast int10  DECLARE @EinzelSql nvarchar(max)11  DECLARE @IsToCreate BIT12  DECLARE @ExistingConstraints QBM_YCursorBuffer13  DECLARE @ConstraintName varchar(30)14  DECLARE @DebugLevel char(1) = 'W'15  DECLARE @Debugmessage nvarchar(4000)16  DECLARE @DebugSwitch int = 017  SET XACT_ABORT OFF18  BEGIN TRY19    IF NOT EXISTS(20      SELECT TOP 1 121      FROM information_schema.tables22      WHERE23        table_name = @Table AND table_type IN('BASE TABLE'))24    BEGIN25      RETURN26    END27    INSERT INTO @ExistingConstraints(Ident1,28    ContentFull)29    SELECT30      x.FKName,31      CONCAT('alter table ',32      x.ChildTable,33      ' with nocheck add constraint ',34      x.FKName,35      ' foreign Key (',36      x.ChildColumn,37      ') references ',38      x.ParentTable,39      ' (',40      x.ParentColumn,41      ') on delete ',42    CASE x.delete_referential_action_desc collate database_default43      WHEN 'NO_ACTION' THEN44    'no action'45      WHEN 'SET_NULL' THEN46    'set null'47      WHEN 'CASCADE' THEN48    'cascade'49    ELSE x.delete_referential_action_desc50    END,51    ' not for Replication') collate database_default AS DefineString52    FROM(53    SELECT54      c.name AS ChildTable,55      fk.name AS FKName,56      o.name AS schemaname,57      fk.delete_referential_action_desc,58      cc.name AS ChildColumn,59      cp.name AS ParentColumn,60      p.name AS ParentTable61    FROM sys.foreign_keys fk62    JOIN sys.objects c63      ON c.object_id = fk.parent_object_id64    JOIN sys.objects p65      ON p.object_id = fk.referenced_object_id66    JOIN sys.schemas o67      ON c.schema_id = o.schema_id AND dbo.QBM_FGIObjectIsDropable(o.schema_id) =168    JOIN DialogTable dt69      WITH(readpast)70      ON c.name = dt.TableName collate database_default71    JOIN sys.foreign_key_columns fkc72      ON fk.object_id = fkc.constraint_object_id73    JOIN sys.columns cc74      ON cc.object_id = c.object_id AND cc.column_id = fkc.parent_column_id75    JOIN sys.columns cp76      ON cp.object_id = p.object_id AND cp.column_id = fkc.referenced_column_id77    WHERE78      fk.type = 'F' AND c.name = @Table collate database_default) AS x79    INSERT INTO @ConstraintDefs(ContentFull)80    SELECT81      dbo.QBM_FSQFKCheckFix(r.UID_QBMRelation,82      r.GenerateChild,83      'dummy')84    FROM QBM_VQBMRelation r85    WHERE86      r.Childtable = @table AND r.GenerateChild > ' ' AND r.GenerateChild LIKE 'C%'87    SELECT @ElementCount = @@ROWCOUNT88    SELECT @ElementIndex = @@IDENTITY - @ElementCount +189    SELECT @ElementLast = @@IDENTITY90    UPDATE @ExistingConstraints91    SET Bit1 = 192    FROM @ExistingConstraints e93    LEFT94    OUTER95    JOIN @ConstraintDefs n96      ON e.ContentFull = n.ContentFull97    WHERE98      n.ContentFull IS NULL99    UPDATE @ConstraintDefs100    SET Bit1 = 1101    FROM @ConstraintDefs n102    LEFT103    OUTER104    JOIN @ExistingConstraints e105      ON e.ContentFull = n.ContentFull106    WHERE107      e.ContentFull IS NULL108    WHILE EXISTS(109    SELECT TOP 1 1110    FROM @ExistingConstraints e111    WHERE112      e.Bit1 = 1)113    BEGIN114      SELECT TOP 1 @ConstraintName = e.Ident1115      FROM @ExistingConstraints e116      WHERE117        e.Bit1 = 1118      IF @debugSwitch > 0119      BEGIN120        print CONCAT('droppen ',121        @ConstraintName)122      END123      EXEC QBM_PConstraintFKDrop @Table,124        @ConstraintName125      DELETE @ExistingConstraints126      WHERE127        Ident1 = @ConstraintName128    END129    WHILE @ElementIndex <= @ElementLast130    BEGIN131      SELECT132        TOP 1 @EinzelSql = bu.ContentFull,133        @IsToCreate = bu.Bit1134      FROM @ConstraintDefs bu135      WHERE136        bu.ElementIndex = @ElementIndex137      IF @debugSwitch > 0138      BEGIN139        print CONCAT('create',140        str(@IsToCreate),141        ' ',142        @EinzelSql)143      END144      IF @IsToCreate = 1145      BEGIN146        EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @EinzelSql,147          @LockTimeout_ms = 2000,148          @MaxWaitTimeForLock_s = 15.0,149          @ProcIDForJournal = DEFAULT,150          @HandleErrorSilent = 0,151          @DeadlockPriority = 5,152          @ExecuteWithTransact = 0153      END154      EXEC QBM_PSessionErrorClean155      SELECT @ElementIndex += 1156    END157  END TRY158  BEGIN CATCH159    EXEC QBM_PSessionErrorAdd DEFAULT160    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()161    RAISERROR(@Rethrow,162    18,163    1)164      WITH NOWAIT165  END CATCH166END
Open raw exported source
SQL ยท Raw26 lines
1          create   procedure QBM_PMakeConstraint (@Table nvarchar(32)  ) as begin   declare @ConstraintDefs QBM_YCursorBuffer declare @ElementCount2 int declare @ElementIndex int declare @ElementLast int declare @EinzelSql nvarchar(max) declare @IsToCreate bit declare @ExistingConstraints QBM_YCursorBuffer3 declare @ConstraintName varchar(30) declare @DebugLevel char(1) = 'W' declare @Debugmessage nvarchar(4000) declare @DebugSwitch int = 0 SET XACT_ABORT4 OFF BEGIN TRY      if not exists (select top 1 1 from information_schema.tables where table_name = @Table and table_type in ('BASE TABLE') ) begin  return5 end insert into @ExistingConstraints(Ident1  , ContentFull) select x.FKName , concat('alter table ' ,x.ChildTable,' with nocheck add constraint ' ,x.FKName6,' foreign Key (' ,x.ChildColumn,') references ' ,x.ParentTable,' (' ,x.ParentColumn,') on delete ' , case x.delete_referential_action_desc collate database_default7 when 'NO_ACTION' then 'no action' when 'SET_NULL' then 'set null' when 'CASCADE' then 'cascade' else x.delete_referential_action_desc  end ,' not for Replication'8 ) collate database_default as DefineString from ( select c.name as ChildTable, fk.name as FKName, o.name as schemaname, fk.delete_referential_action_desc9, cc.name as ChildColumn, cp.name as ParentColumn, p.name as ParentTable from sys.foreign_keys fk join sys.objects c on c.object_id = fk.parent_object_id10 join sys.objects p on p.object_id = fk.referenced_object_id join sys.schemas o on c.schema_id = o.schema_id and dbo.QBM_FGIObjectIsDropable (o.schema_id11)=1 join DialogTable dt with (readpast) on c.name = dt.TableName collate database_default join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id12 join sys.columns cc on cc.object_id = c.object_id and cc.column_id = fkc.parent_column_id join sys.columns cp on cp.object_id = p.object_id and cp.column_id13 = fkc.referenced_column_id where fk.type = 'F'  and c.name = @Table collate database_default  ) as x  insert into @ConstraintDefs(ContentFull) select 14dbo.QBM_FSQFKCheckFix (r.UID_QBMRelation, r.GenerateChild, 'dummy')  from QBM_VQBMRelation r where r.Childtable = @table and r.GenerateChild > ' ' and 15r.GenerateChild like 'C%' select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY update16 @ExistingConstraints set Bit1 = 1 from @ExistingConstraints e left outer join @ConstraintDefs n on e.ContentFull = n.ContentFull where n.ContentFull is17 null update @ConstraintDefs set Bit1 = 1 from @ConstraintDefs n left outer join @ExistingConstraints e on e.ContentFull = n.ContentFull where e.ContentFull18 is null while exists (select top 1 1 from @ExistingConstraints e where e.Bit1 = 1 ) begin select top 1 @ConstraintName = e.Ident1 from @ExistingConstraints19 e where e.Bit1 = 1 if @debugSwitch > 0 begin print concat('droppen ' , @ConstraintName) end exec QBM_PConstraintFKDrop @Table, @ConstraintName delete 20@ExistingConstraints where Ident1 = @ConstraintName end  while @ElementIndex <= @ElementLast begin select top 1 @EinzelSql = bu.ContentFull , @IsToCreate21 = bu.Bit1 from @ConstraintDefs bu where bu.ElementIndex = @ElementIndex if @debugSwitch > 0 begin print concat('create' , str(@IsToCreate), ' ', @EinzelSql22) end if @IsToCreate = 1 begin exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @EinzelSql , @LockTimeout_ms = 2000 , @MaxWaitTimeForLock_s = 15.0 , @ProcIDForJournal23 = default , @HandleErrorSilent = 0 , @DeadlockPriority = 5  , @ExecuteWithTransact = 0 end exec QBM_PSessionErrorClean select @ElementIndex += 1 end END24 TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT25 END CATCH end 26