If this is a "one-off" - i.e. you are only going to do it once and it's not going to be in a stored procedure that gets called time and time again, then you could use a
temporary table[
^]
E.g.
SELECT [TableName], SF.*
INTO #dynamicTable
FROM SchemaModel SM
INNER JOIN SchemaFields SF ON SF.SchemaID = SM.SchemaID
which you can query just like any other table
select * from #dynamicTable
If you are going to use temporary tables though you must be aware of their
scope[
^] - i.e. when they will and will not be available.
BUT - If you are likely to look at this data often then create a
View[
^] of the data e.g.
CREATE VIEW dynamicTable AS
SELECT [TableName], SF.*
FROM SchemaModel SM
INNER JOIN SchemaFields SF ON SF.SchemaID = SM.SchemaID
Although the view doesn't physically exist you can query it as you would any other table
select * from dynamicTable
including being able to join to it
select * from dynamicTable DT
INNER JOIN SchemaFields SF on DT.SchemaID = SF.SchemaID