How to delete a custom table column in dell one identtiymananger 7.0

Question

fatih.eroglu · Accepted Answer

Hello, 
 In Version 7 the removal of a single custom column can be accomplished by a built in stored procedure, available in the database called: QBM_PColumnDrop 
 Hope that helps. 
 Regards Fatih

Markus Weiss-Ehlers · Answer

DISCLAIMER 
 Use at your own risk. Start with a backup of your database first, before you try to use these scripts. Once they are used there is no way back without having a backup. 
 Having said that, take a look at the attached SQL script that helps you to delete a custom table column in Version 6. 
 
 2500.Delete_CustomColumn.sql 
 -- Remove of a custom defined column

SET NOCOUNT ON

declare @table varchar(200)
declare @column varchar(200)
declare @isvidefined bit
declare @count int
declare @uid_dialogcolumn char(38)
declare @cmd varchar(8000)

-----------------------------------------------------

-- Define the table and column
select @table = 'SE_Kundendaten'
select @column = 'SE_Column2'

-----------------------------------------------------

-- Is this a custom column?
select @isvidefined = isvidefined from dialogcolumn
 where
 tablename = @table and columnname = @column
 or not exists (
 select 1 from dialogcolumn where tablename = @table and columnname = @column
 )
 order by 1

if @isvidefined = 1
begin
	print 'The defined column doesn''t exist or is not a custom column.'
	goto theend
end


-- Find the UID of the column
select @uid_dialogcolumn = uid_dialogcolumn from dialogcolumn where tablename = @table and columnname = @column


-- Are there FK relations from this column to other tables?
select @count = count(*) from dialogtablerelation tr
 join dialogcolumnrelation cr on tr.relationid = cr.relationid
 join dialogcolumn c on cr.uid_parentcolumn = c.uid_dialogcolumn
 where c.uid_dialogcolumn = @uid_dialogcolumn

if @count > 0
begin
	print 'The column ' + @column + ' in the table ' + @table + ' can not be deleted because there are fk relations from other tables to this column:'
	print ''

	select c2.tablename, c2.columnname from dialogtablerelation tr
	join dialogcolumnrelation cr on tr.relationid = cr.relationid
	join dialogcolumn c1 on cr.uid_parentcolumn = c1.uid_dialogcolumn
	join dialogcolumn c2 on cr.uid_childcolumn = c2.uid_dialogcolumn
	where c1.uid_dialogcolumn = @uid_dialogcolumn

	print ''
	print 'Please remove these columns 1st before you can delete the column ' + @column + ' in the table ' + @table + '.'
	goto theend
end


-- Is this the last column of the table?
select @count = count(*) from dialogcolumn where tablename = @table

if @count = 1
begin
	print 'The column ' + @column + ' in the table ' + @table + ' can not be deleted because this is the last column in this table.'
	goto theend
end


-- Repeat for every dataset which depends on this DialogColumn and deletion
declare step_tablerelation scroll cursor for
	select 'delete from ' + tr.childtable + ' where ' + c.columnname + ' = ''' + rtrim(@uid_dialogcolumn) + ''''
	from dialogtablerelation tr
	join dialogcolumnrelation cr on tr.relationid = cr.relationid
	join dialogcolumn c on cr.uid_childcolumn = c.uid_dialogcolumn
	where
 tr.parenttable = 'dialogcolumn'
 and not (tr.parentrestriction = 'DC' and tr.parentexecuteby = 'T')
	for read only

	open step_tablerelation

	fetch first from step_tablerelation into @cmd

	while (@@fetch_status <> -1)
	begin
 exec(@cmd)

 fetch next from step_tablerelation into @cmd
	end

close step_tablerelation
deallocate step_tablerelation

-- Delete the column in DialogColumn
delete from dialogcolumn where uid_dialogcolumn = @uid_dialogcolumn

-- Delete the TableRelations
delete from dialogtablerelation where relationid not in (
	select relationid from dialogcolumnrelation
)

/*
-- Recalculate the triggers to be sure that the column is not used by any tablerelation-triggers
select @cmd = 'exec vid_DBSchedulerInsert ''COMMONMAKERITRIGGER'', ''' + @table + ''', '''', ''12345'''
exec(@cmd)

select @cmd = 'exec vid_DBScheduler 9000'
exec(@cmd)
*/

-- Delete the column in the schema
exec vid_DropColumn @table, @column

theend: