dbo.QBM_PColumnCustomRemove

1    create   procedure QBM_PColumnCustomRemove ( @TableName varchar(30) , @ColumnName varchar(30) , @DisplayAllMessages bit = 0 ) as begin declare
2 @IsTableToDrop bit = 0 declare @UID_DialogColumn varchar(38) = null declare @UID_DialogTable varchar(38) = null declare @ObjectKeyDialogTable varchar(138
3) declare @TableType varchar(1) declare @Sammler QBM_YCursorBuffer    declare @Step varchar(30) declare @SQLCmd nvarchar(max) declare @ErrorMessage nvarchar
4(4000)  declare @ElementCount int declare @ElementIndex int declare @MessageType varchar(1) declare @ChildCmd QBM_YCursorBuffer declare @TrancountAtStart
5 int declare @ErrorBuffer QBM_YSessionError SET XACT_ABORT OFF BEGIN TRY set nocount on select top 1 @UID_DialogTable = t.UID_DialogTable , @UID_DialogColumn
6 = c.UID_DialogColumn , @ObjectKeyDialogTable = t.XObjectKey , @TableType = t.TableType from DialogTable t left outer join DialogColumn c on t.UID_DialogTable
7 = c.UID_DialogTable and c.ColumnName = @ColumnName where t.TableName = @TableName select @Step = 'Checking transaction level' if @DisplayAllMessages =
8 1 begin print @step end select @TrancountAtStart = @@TRANCOUNT if @TrancountAtStart > 0 begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select 
9@Step, 0, 'QBM_PColumnCustomRemove runs within transaction' end select @Step = 'Checking ProductionLevel' if @DisplayAllMessages = 1 begin print @step 
10end if exists (select top 1 1 from DialogDatabase db with (readpast) where db.IsMainDatabase = 1 and db.ProductionLevel > 1 ) begin insert into @Sammler
11 (Ident1, Bit1, LongIdent1 ) select @Step, 1, 'QBM_PColumnCustomRemove is only allowed in Dev and QA environments' end select @Step = 'Checking QBM-Tables'
12 if @DisplayAllMessages = 1 begin print @step end if @UID_DialogTable is null begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 1, 'Table not found in DialogTable'
13 end if @UID_DialogColumn is null begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 1, 'Column not found in DialogColumn' end select
14 @Step = 'Checking Table-Type' if @DisplayAllMessages = 1 begin print @step end if @TableType not in ('T', 'B', 'V') begin insert into @Sammler (Ident1
15, Bit1, LongIdent1 ) select @Step, 1, 'TableType not in (B, T, V)' end select @Step = 'Checking schema' if @DisplayAllMessages = 1 begin print @step end
16 if not exists (select top 1 1 from INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = @TableName ) begin insert into @Sammler (Ident1, Bit1, LongIdent1 
17) select @Step, 1, 'Table not found in schema' end if not exists (select top 1 1 from INFORMATION_SCHEMA.COLUMNS c where c.TABLE_NAME = @TableName and 
18c.COLUMN_NAME = @ColumnName ) begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 1, 'Column not found in schema' end   select @Step =
19 'Checking ownership of column' if @DisplayAllMessages = 1 begin print @step end if dbo.QBM_FCVGUIDToModuleOwner(@UID_DialogColumn) <> dbo.QBM_FGIDBOwner
20() begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 1, concat('You are not the owner of ' , @TableName , '.' , @ColumnName) end select
21 @Step = 'Checking Table to Drop' if @DisplayAllMessages = 1 begin print @step end if 0 = (select COUNT(*) from INFORMATION_SCHEMA.COLUMNS c where c.TABLE_NAME
22 = @tablename and c.COLUMN_NAME <> @ColumnName ) begin select @IsTableToDrop = 1 insert into @Sammler (Ident1, Bit1, LongIdent1) select @Step, 0, concat
23('Last column of table, table will be dropped: ' , @TableName)  end select @Step = 'Checking PK membership' if @DisplayAllMessages = 1 begin print @step
24 end if exists (select top 1 1 from sys.key_constraints c join sys.tables t on c.parent_object_id = t.object_id join sys.indexes i on c.name = i.name  
25and i.is_primary_key = 1  and i.is_hypothetical = 0  and i.type not in (3,4,5,6) join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id
26 = i.index_id join sys.columns co on t.object_id = co.object_id and co.column_id = ic.column_id where t.name = @TableName and co.name = @ColumnName ) begin
27 insert into @Sammler (Ident1, Bit1, LongIdent1) select @Step, 0, concat('Column is PK-member of table, table will be dropped' , @TableName)  select @IsTableToDrop
28 = 1 select @Step = 'Checking Table empty' if @DisplayAllMessages = 1 begin print @step end if dbo.QBM_FGITableCountAll(@tablename) > 0 begin insert into
29 @Sammler (Ident1, Bit1, LongIdent1, ContentFull) select @Step, 1, 'Table is not empty' , concat('delete ' , @tablename) end end if @IsTableToDrop = 1 
30begin  select @Step = 'Checking ownership of table' if @DisplayAllMessages = 1 begin print @step end if dbo.QBM_FCVGUIDToModuleOwner(@UID_DialogTable) 
31<> dbo.QBM_FGIDBOwner() begin insert into @Sammler (Ident1, Bit1, LongIdent1 ) select @Step, 1, concat('You are not the owner of table ' , @TableName )
32 end end select @Step = 'Checking template references' if @DisplayAllMessages = 1 begin print @step end insert into @Sammler (Ident1, Bit1, LongIdent1 
33, ContentFull) select @Step, 1, concat('Column is referenced in template ' , t.TableName , '.' , cr.ColumnName) , concat('''' , cr.Template ) from DialogNotification
34 n join DialogColumn cr on n.UID_DialogColumnSubscriber = cr.UID_DialogColumn join DialogTable t on cr.UID_DialogTable = t.UID_DialogTable where n.UID_DialogColumnSender
35 = @UID_DialogColumn  and n.UID_DialogColumnSubscriber <> @UID_DialogColumn select @Step = 'Checking referential integrity' if @DisplayAllMessages = 1 
36begin print @step end insert into @Sammler (Ident1, Bit1, LongIdent1 , ContentFull) select @Step, 1, concat('Column is referenced as parent in RI ' , r.RelationID
37 ) , concat('delete QBMRelation where RelationID = ''' , r.RelationID , '''')  from QBM_VQBMRelation r where ParentTable = @TableName and ParentColumn 
38= @ColumnName select @Step = 'Checking dynamic RI' if @DisplayAllMessages = 1 begin print @step end insert into @Sammler (Ident1, Bit1, LongIdent1 , ContentFull
39) select @Step, 1, concat('Column is referenced as parent in  ' , cr.UID_DialogTable , '.' , cr.ColumnName) , concat('delete DialogValidDynamicRef where UID_DialogColumn = '''
40 , r.UID_DialogColumn , ''' and UID_DialogTableReference = ''' , r.UID_DialogTableReference , '''')  from DialogValidDynamicRef r join DialogTable t on
41 r.UID_DialogTableReference = t.UID_DialogTable and t.TableName = @TableName join DialogColumn c on t.UID_DialogTable = c.UID_DialogTable and c.ColumnName
42 = @ColumnName and c.ColumnName = 'XObjectkey' join DialogColumn cr on r.UID_DialogColumn = cr.UID_DialogColumn  select @Step = 'Checking indexes' if @DisplayAllMessages
43 = 1 begin print @step end insert into @Sammler (Ident1, Bit1, LongIdent1 , ContentFull) select @Step, 1, concat('Column is contained in Index ' , t.name
44 , '.' , i.name) , concat('exec QBM_PIndexDrop ''' , t.name , ''', ''' , i.name , '''
45	-- remove ' , @ColumnName , ' in the following line
46	' , dbo.QBM_FSQIndexDef_i
47(t.name, i.name, 1) ) from sys.index_columns ik with (nolock) join sys.columns c with (nolock) on ik.column_id = c.column_id and c.name = @columnname and
48 c.object_id = ik.object_id join sys.tables t on t.object_id = ik.object_id and t.name = @TableName join sys.indexes i on ik.index_id = i.index_id and 
49i.object_id = t.object_id where i.is_primary_key = 0     if exists (select top 1 1 from @Sammler s where s.Bit1 = 1 ) begin if @DisplayAllMessages = 1 
50begin select s.Ident1 as Step , s.Bit1 as IsError , s.LongIdent1 as Remarks , isnull(s.ContentFull, '') as RepairStatement from @Sammler s order by s.ElementIndex
51 select top 1 @ErrorMessage = left(concat('#LDS#Column will not be dropped. 1st error was {0}, repair hint {2} .|' , s.LongIdent1 , '|' , s.ContentFull
52 , '|' ) , 4000) from @sammler s where s.Bit1 = 1 raiserror(@ErrorMessage, 18, 1) with nowait end else begin  select @ElementCount = count(*) from @Sammler
53 s select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @ErrorMessage = left(concat('#LDS#Column will not be dropped, step {0}, message: {1}, repair hint {2} .|'
54 , bu.Ident1 , '|' , bu.LongIdent1 , '|' , bu.ContentFull , '|' ) , 4000) , @MessageType = case bu.Bit1 when 1 then 'E' else 'I' end from @Sammler bu where
55 bu.ElementIndex = @ElementIndex exec QBM_PJournal @Errormessage, @@procid, @MessageType, 'I'  exec QBM_PWaitForSeconds 0.005 insert into @ErrorBuffer 
56(ErrorMessage, ErrorSeverity, ErrorState, ErrorNumber , ProcedureName, ProcedureLine, MessageDate , GenProcID, RepeatCounter, IsReThrow, SourceCode) select
57 @Errormessage, 18, 1, 50000 , object_name(@@procid), 0, GETUTCDATE() , null, 0, 0, null select @ElementIndex += 1 end  select top 1 @ErrorMessage = concat
58('#LDS#Column will not be dropped, {0} message(s) logged in journal.|' , str(@ElementCount) , '|' ) insert into @ErrorBuffer (ErrorMessage, ErrorSeverity
59, ErrorState, ErrorNumber , ProcedureName, ProcedureLine, MessageDate , GenProcID, RepeatCounter, IsReThrow, SourceCode) select @Errormessage, 18, 1, 50000
60 , object_name(@@procid), 0, GETUTCDATE() , null, 0, 0, null exec QBM_PSessionErrorAdd @Errorbuffer raiserror(@ErrorMessage, 18, 1) with nowait end  end
61 else begin if @DisplayAllMessages = 1 begin print 'try to drop column' end select s.Ident1 as Step , s.Bit1 as IsError from @Sammler s order by s.ElementIndex
62 end begin transaction if @IsTableToDrop = 1 begin delete DialogColumnGroupRight from DialogColumnGroupRight gr join DialogColumn c on gr.UID_DialogColumn
63 = c.UID_DialogColumn where c.UID_DialogTable = @UID_DialogTable delete DialogColumn from DialogColumn c where c.UID_DialogTable = @UID_DialogTable  insert
64 into @ChildCmd(ContentFull) select concat( 'delete ' , r.ChildTable , '
65					from ' , r.ChildTable , ' c 
66					where c.' , r.ChildColumn , ' = ''', 
67@UID_DialogTable, '''
68					' ) from QBM_VQBMRelation r where ParentTable = 'dialogtable' select @ElementCount = @@rowcount  select @ElementIndex = 1 while
69 @ElementIndex <= @ElementCount begin select top 1 @SQLCmd = bu.ContentFull from @ChildCmd bu where bu.ElementIndex = @ElementIndex exec sp_executesql 
70@sqlcmd select @ElementIndex += 1 end delete DialogTable from DialogTable a where a.UID_DialogTable = @UID_DialogTable select @SQLCmd = concat('drop table '
71 , @TableName) exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @sqlcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal
72 = default , @HandleErrorSilent = 0 end else begin exec QBM_PColumnDrop @TableName, @ColumnName end if @@TRANCOUNT = @TrancountAtStart + 1 begin commit
73 Transaction end else begin if @@TRANCOUNT > 0 begin rollback transaction end end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default  if @@TRANCOUNT
74 > 0 begin rollback end RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: return end  
75