dbo.QBM_PConstraintChange

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) declare
3 @isDisabled bit declare @sqlcmd nvarchar(max) declare @elements QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int      SET XACT_ABORT
4 OFF BEGIN TRY insert into @elements(LongIdent1 , Bit1 , Ident2 , Ident3 ) select distinct fk.name as ConstraintName , fk.is_disabled as isDisabled, c.name
5 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_id
6 = fk.referenced_object_id where fk.type = 'F' and fk.name like @Constraintpattern and c.name like @ChildTablePattern and p.name like @ParentTablePattern
7 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  from
9 @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_LLP
11 @SQLStatement = @SQLcmd , @LockTimeout_ms = 2500  , @MaxWaitTimeForLock_s = 10 , @ProcIDForJournal = default , @HandleErrorSilent = 0   , @ExecuteWithTransact
12 = 0 select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow
13() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH end 
14