Select from list of tables in SQL Server

Question

I have a list of tables and I ultimately want to build up some JSON that looks like this:

{
    "Table1": [
        { "col1": "value", "col2": "value" },
        { "col1": "value", "col2": "value" },
        { "col1": "value", "col2": "value" },
        { "col1": "value", "col2": "value" }
    ],
    "Table2": [
        { "col1": "value", "col2": "value" },
        { "col1": "value", "col2": "value" },
        { "col1": "value", "col2": "value" },
        { "col1": "value", "col2": "value" }
    ],
    "Table3": [
        { "col1": "value", "col2": "value" },
        { "col1": "value", "col2": "value" },
        { "col1": "value", "col2": "value" },
        { "col1": "value", "col2": "value" }
    ]
}

Creating the JSON is not the problem for me (although I can't use FOR JSON on SQL Server 2014) and I even have a naive implementation for the SQL:

declare @tables table (
    tabname varchar(100)
)
insert into @tables(tabname)
select 'Table1' union all
select 'Table2' union all
select 'Table3'

/* Master dataset */
select tabname from @tables 

declare tabcur cursor for select tabname from @tables 

open tabcur

declare @tabname varchar(100)

fetch tabcur into @tabname
while @@FETCH_STATUS = 0
begin
    /* Detail dataset */
    execute ('select * from [' + @tabname + ']')
    fetch tabcur into @tabname
end

close tabcur
deallocate tabcur

The idea behind this is that I get a list of tables as master dataset and for each row I also get a detail dataset. I can match those by index, or eliminate the master dataset by adding the table name to the detail dataset.

Whichever will work. But my actual list contains 724 tables and the performance is lousy.

So then I changed it to build a SQL string of union all statements instead and only execute that string after the cursor is deallocated. That mostly fixed my performance problem.

But it still seems like a very nineties way of doing this. With all the interesting things that SQL Server has added since then, I would imagine there must be a simpler way. I am basically looking to use the master dataset as input to a second dataset that contains the rows from all the tables (the columns I'm interested in are the same in all the tables, so they will work as one result set).

My next attempt was to create a function that accepts the table name as parameter and returns the result set. I thought I could cross apply to that:

select tabname, result.col1, result.col2
from @tables 
cross apply dbo.SelectFromTable(@tables.tabname) result

But that was stillborn, because SQL Server does not allow dynamic SQL within a function.

So is there a well-performing way to do this without a cursor?

Answer 1

Its not pretty and you really should be looking at a different design, but if you are stuck doing it this way you can avoid the cursor with for xml (which is used to put all rows into one delimited column) and stuff (which is used to remove the leading delimiter, in this case ' union all ':

declare @tables table (
    tabname varchar(100)
);
insert into @tables(tabname)
select 'Table1' union all
select 'Table2' union all
select 'Table3';

select stuff((select ' union all select * from [' + tabname + ']'
                from @tables
                for xml path('')
                )
            ,1,11,'') as query

Which outputs:

select * from [Table1] union all select * from [Table2] union all select * from [Table3]