How to delete a custom table column in dell one identtiymananger 7.0
- Products
- Solutions
- Resources
- Trials
- Support
- Partners
- Communities
How to delete a custom table column in dell one identtiymananger 7.0
Having said that, take a look at the attached SQL script that helps you to delete a custom table column in Version 6.
-- 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:
Having said that, take a look at the attached SQL script that helps you to delete a custom table column in Version 6.
-- 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: