
create table #tables (s varchar(255), t varchar(255))
go 
declare @xmlChar nvarchar(max), @xml xml
set @xmlChar = 
(
	select cast(replace(replace(replace(replace(cast([xml] as nvarchar(max)),N'<?xml version="1.0" encoding="utf-8" standalone="yes"?>',N''),N'<?xml version="1.0" encoding="utf-16" standalone="yes"?>',N''),N'<?xml version="1.0" encoding="utf-8"?>',N''),N'<?xml version="1.0" encoding="utf-16"?>',N'') as xml)
	from VCLib.T_ENTITIES
	FOR XML PATH(''), ROOT('ENTITIES')
)
set @xml = cast(@xmlChar as xml)

insert into #tables(s,t)
(select distinct Tab.n.value('../../@Schema', 'varchar(255)') as [s]
		, Tab.n.value('./@name', 'varchar(255)') [t]
		from @xml.nodes('/ENTITIES/Entity/DbMapping/Table') as Tab(N)
		
		union

		select distinct Tab.n.value('../../../@Schema', 'varchar(255)') as [s]
		, Tab.n.value('./@RefTable', 'varchar(255)') [t]
		from @xml.nodes('/ENTITIES/Entity/DbMapping/Table/Map') as Tab(N) )


declare @schema varchar(255), @table varchar(255), @command nvarchar(max)
DECLARE @CURSOR CURSOR


SET @CURSOR  = CURSOR
FOR
select  s,t 
from #tables
join ( select table_name as t2 from information_schema.tables where TABLE_TYPE='BASE TABLE' and TABLE_SCHEMA = 'dbo'
		union select v.name as t2 from sys.views v join sys.schemas s on v.schema_id=s.schema_id where s.name = 'dbo') t3 on t3.t2=t
where t<>'T_ENTITIES' and t<>'T_USERS' 

OPEN @CURSOR
FETCH NEXT FROM @CURSOR INTO @schema, @table
WHILE @@FETCH_STATUS = 0
BEGIN

set @command = 'alter schema '+@schema+' transfer dbo.'+@table
exec sp_executesql @command

FETCH NEXT FROM @CURSOR INTO @schema, @table
END
CLOSE @CURSOR


drop table #tables