dbo.QBM_PConstraintFKDrop

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 @lauf
3 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 , ConstraintName
5 varchar(64) collate database_default , OwnerName varchar(64) collate database_default ) declare @ElementsAffected int = 0 declare @JournalMessage nvarchar
6(1000) declare @JournalProcedureExists bit = 0 declare @CountElementsChanged int = 0 declare @FehlerAufgetreten bit = 0 SET XACT_ABORT OFF BEGIN TRY if
7 exists (select top 1 1 from sys.objects o where o.name = 'QBM_PJournal' and type = 'P' ) begin select @JournalProcedureExists = 1 end  if exists (select
8 top 1 1 from sys.tables t where t.name = 'DialogTable' collate database_default ) begin  select @SQLcmd = '
9			select c.name, fk.name, o.name
10			from sys.foreign_keys fk join sys.objects c on c.object_id = fk.parent_object_id
11									join sys.objects p on p.object_id = fk.referenced_object_id
12									join sys.schemas o on c.schema_id = o.schema_id
13														and dbo.QBM_FGIObjectIsDropable (o.schema_id)=1
14									join DialogTable dt with (readpast) on c.name = dt.TableName  collate database_default
15			where fk.type = ''F''
16				and fk.name like '''
17 + @Constraintpattern + '''  collate database_default
18				and c.name like ''' + @Tablepattern+ '''  collate database_default
19				and p.name like ''' 
20+ @ParentTablePattern+ '''  collate database_default
21			' end else begin  select @SQLcmd = '
22			select c.name, fk.name, o.name
23			from sys.foreign_keys fk join sys.objects c on c.object_id = fk.parent_object_id
24									join sys.objects p on p.object_id = fk.referenced_object_id
25									join sys.schemas o on c.schema_id = o.schema_id
26														and dbo.QBM_FGIObjectIsDropable (o.schema_id)=1
27													and o.name = ''dbo''  collate database_default
28			where fk.type = ''F''  collate database_default
29				and fk.name like '''
30 + @Constraintpattern + '''  collate database_default
31				and c.name like ''' + @Tablepattern+ '''  collate database_default
32				and p.name like ''' 
33+ @ParentTablePattern+ '''  collate database_default
34			' end if @DebugSwitch > 0 begin print @sqlcmd end insert into @elementsToDrop(TableName, ConstraintName
35, OwnerName) exec sp_executesql @sqlcmd select @ElementsAffected = @@ROWCOUNT select @LockRetry = 5 while @LockRetry > 0 begin  select @lauf = 1 select
36 @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 , nchar
39(13) , nchar(10) ) if @JournalProcedureExists = 1 begin exec QBM_PJournal @SQLcmd, @@PROCID, 'D', @DebugLevel end if @DebugSwitch > 0 begin print @sqlcmd
40 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.TableName
42 = @TableName and td.ConstraintName = @constraintname and td.OwnerName = @ownerName select @CountElementsChanged += 1 exec QBM_PSessionErrorClean END TRY
43 BEGIN CATCH exec QBM_PSessionErrorAdd default, @sqlcmd if dbo.QBM_FGISessionErrorIsDeadlock(default) = 1 begin select @LockRetry -= 1 if @JournalProcedureExists
44 = 1 begin exec QBM_PJournal 'SessionError', @@PROCID, 'W', @DebugLevel end select @Errormessage = null waitfor delay '00:00:00.500' end else begin select
45 @FehlerAufgetreten = 1 if @JournalProcedureExists = 1 begin exec QBM_PJournal 'SessionError', @@PROCID, 'E', @DebugLevel end select @lauf = 0 select @LockRetry
46 = 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 from
48 DialogDatabase where IsMainDatabase = 1 and UpdatePhase <> 0) and not exists (select top 1 1 from sys.tables t with (readpast) join sys.columns c with
49 (readpast) on t.object_id = c.object_id where t.name = 'DialogDatabase' and c.name = 'InitialMigrationRunning' ) begin exec QBM_PJournal @JournalMessage
50, @@procid, @MessageType, @DebugLevel, 20 end else begin  exec QBM_PJournal @JournalMessage, @@procid, 'I', 'I' end end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd
51 default END CATCH if @FehlerAufgetreten = 1 begin declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT
52 end end 
53