use tempdb;
go

create table dbo.t1 (id int primary key, external_id int, some_data int);
create table dbo.t2 (id int primary key, external_id int, some_data int);
create table dbo.t3 (id int primary key, external_id int, some_data int);

create table dbo.t (id int primary key, external_id int, tables_list nvarchar(max));
go

insert into dbo.t1
values
 (1, 1, 1), (2, 1, 1), (3, 2, 2);

insert into dbo.t2
values
 (1, 1, 10), (2, 1, 10), (3, 2, 20);

insert into dbo.t3
values
 (1, 1, 100), (2, 1, 100), (3, 2, 200);

insert into dbo.t
values
 (1, 1, 't1, t2'), (2, 2, 't2, t3');
go

create function dbo.fnGetDataFromTable
(
 @external_id int,
 @table_name sysname
)
returns table
as
return (
 select id, some_data from dbo.t1 where @table_name = 't1' and external_id = @external_id
 union all
 select id, some_data from dbo.t2 where @table_name = 't2' and external_id = @external_id
 union all
 select id, some_data from dbo.t3 where @table_name = 't3' and external_id = @external_id
);
go

set statistics xml on;

select
 t.id, t.external_id, t.tables_list, c.table_name, d.*
from
 dbo.t cross apply
 (select cast('<item t = "' + replace(t.tables_list, ', ', '" /><item t = "') + '" />' as xml)) a(x) cross apply
 a.x.nodes('item') b(n) cross apply
 (select ltrim(rtrim(b.n.value('@t', 'sysname')))) c(table_name) cross apply
 dbo.fnGetDataFromTable(t.external_id, c.table_name) d;

 set statistics xml off;
 go

drop function dbo.fnGetDataFromTable;
drop table dbo.t, dbo.t1, dbo.t2, dbo.t3;
go