declare @type varchar(30);

if object_id('tempdb..#types') is not null
	drop table #types

select 'text' as [type_name]
into #types 
union
select 'ntext' union
select 'image'

DECLARE TypeCursor CURSOR FOR
	SELECT [type_name] FROM #types

OPEN TypeCursor

	FETCH NEXT FROM TypeCursor INTO @type
	WHILE @@FETCH_STATUS = 0
	BEGIN

		declare @field_name varchar(256), @is_nullable bit, @table_name varchar(256), @schema as varchar(32);

		if object_id('tempdb..#text_columns') is not null
			drop table #text_columns
		
		select e.[schema], st.[name] as table_name, ea.[name] as field_name, sc.is_nullable 
		into #text_columns
		from vclib.T_ENTITIES_ATTRIBUTES ea
		inner join vclib.T_ENTITIES e
			on e.EntityID = ea.EntityID
			and ea.IsAdditional = 0
			and ea.MappingType = 1
		inner join sys.tables st
			on st.[schema_id] = SCHEMA_ID(e.[Schema])
			and st.[object_id] = OBJECT_ID(e.[Schema] + '.' + ea.MappingTable ,'U')
		inner join sys.columns sc
			on sc.[object_id] = st.[object_id]
			and sc.[name] = ea.[Name]
			and sc.user_type_id = TYPE_ID(@type)
		union
		select e.[schema], st.[name] as table_name, ea.[name] as field_name, sc.is_nullable 
		from vclib.T_ENTITIES_ATTRIBUTES ea
		inner join vclib.T_ENTITIES e
			on e.EntityID = ea.EntityID
			and ea.IsAdditional = 0
			and ea.MappingRefTable is not null
			and ea.MappingType = 3
		inner join sys.tables st
			on st.[schema_id] = SCHEMA_ID(e.[Schema])
			and st.[object_id] = OBJECT_ID(e.[Schema] + '.' + ea.MappingRefTable ,'U')
		inner join sys.columns sc
			on sc.[object_id] = st.[object_id]
			and sc.[name] = 'Value'
			and sc.user_type_id = TYPE_ID(@type)

		DECLARE FieldCursor CURSOR FOR
			SELECT [field_name], is_nullable, table_name, [schema] FROM #text_columns

		OPEN FieldCursor

			FETCH NEXT FROM FieldCursor INTO @field_name, @is_nullable, @table_name, @schema;

			WHILE @@FETCH_STATUS = 0
			BEGIN

				DECLARE @sql varchar(1000)
				if(@type = 'text')
					select @sql = 'alter table [' + @schema + '].[' + @table_name + '] alter column [' + @field_name + ']' + ' varchar(max)' + case when @is_nullable = 0 then ' not' else '' end + ' null'
				if(@type = 'ntext')
					select @sql = 'alter table [' + @schema + '].[' + @table_name + '] alter column [' + @field_name + ']' + ' nvarchar(max)' + case when @is_nullable = 0 then ' not' else '' end + ' null'
				if(@type = 'image')
					select @sql = 'alter table [' + @schema + '].[' + @table_name + '] alter column [' + @field_name + ']' + ' varbinary(max)' + case when @is_nullable = 0 then ' not' else '' end + ' null'
				--print @sql
				EXEC (@sql)
				
				FETCH NEXT FROM FieldCursor INTO @field_name, @is_nullable, @table_name, @schema;
			END
		CLOSE FieldCursor
		DEALLOCATE FieldCursor

		FETCH NEXT FROM TypeCursor INTO @type
	END
CLOSE TypeCursor
DEALLOCATE TypeCursor