dbo.QBM_PMakeConstraint

1          create   procedure QBM_PMakeConstraint (@Table nvarchar(32)  ) as begin   declare @ConstraintDefs QBM_YCursorBuffer declare @ElementCount
2 int declare @ElementIndex int declare @ElementLast int declare @EinzelSql nvarchar(max) declare @IsToCreate bit declare @ExistingConstraints QBM_YCursorBuffer
3 declare @ConstraintName varchar(30) declare @DebugLevel char(1) = 'W' declare @Debugmessage nvarchar(4000) declare @DebugSwitch int = 0 SET XACT_ABORT
4 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  return
5 end insert into @ExistingConstraints(Ident1  , ContentFull) select x.FKName , concat('alter table ' ,x.ChildTable,' with nocheck add constraint ' ,x.FKName
6,' foreign Key (' ,x.ChildColumn,') references ' ,x.ParentTable,' (' ,x.ParentColumn,') on delete ' , case x.delete_referential_action_desc collate database_default
7 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_desc
9, 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_id
10 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_id
11)=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_id
12 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_id
13 = 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 update
16 @ExistingConstraints set Bit1 = 1 from @ExistingConstraints e left outer join @ConstraintDefs n on e.ContentFull = n.ContentFull where n.ContentFull is
17 null update @ConstraintDefs set Bit1 = 1 from @ConstraintDefs n left outer join @ExistingConstraints e on e.ContentFull = n.ContentFull where e.ContentFull
18 is null while exists (select top 1 1 from @ExistingConstraints e where e.Bit1 = 1 ) begin select top 1 @ConstraintName = e.Ident1 from @ExistingConstraints
19 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 , @IsToCreate
21 = bu.Bit1 from @ConstraintDefs bu where bu.ElementIndex = @ElementIndex if @debugSwitch > 0 begin print concat('create' , str(@IsToCreate), ' ', @EinzelSql
22) end if @IsToCreate = 1 begin exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @EinzelSql , @LockTimeout_ms = 2000 , @MaxWaitTimeForLock_s = 15.0 , @ProcIDForJournal
23 = default , @HandleErrorSilent = 0 , @DeadlockPriority = 5  , @ExecuteWithTransact = 0 end exec QBM_PSessionErrorClean select @ElementIndex += 1 end END
24 TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT
25 END CATCH end 
26