-- 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: