Pull Column data values from sys.columns from a database having same table structure

Question

I am dealing with the situation in sql server where a database contains the multiple tables with similar table name (ex total_0122, total_0622) and same column. I wanted to calculate the percentage difference from all those tables by taking particular columns data. My approch is

1. list all the tables present in the database with name like %Total_%
2. Then concatenate/join the all the tables together
3. Read the table content
4. lastly find the percentage diffrence.

I am able to list all the tables present in the database and able to concatenate it which is step 1 and 2. for which i have performed

''' SELECT
   sys.columns.name AS ColumnName,
   tables.name AS TableName
   FROM
   sys.columns
   JOIN sys.tables ON
   sys.columns.object_id = tables.object_id
   WHERE
   sys.tables.name LIKE '%total_%

which gives me an output as

 ColumnName    TableName
 email         total_0122
 country       total_0122
 revenue       total_0122
 email         total_0622
 country       total_0622
 revenue       total_0622

I am not able to figure out how to read those tables into one table along with the column values (email, country, revenue...) I am new sql. Any help will be appreciated. Please advice.

Answer 1

You need to construct a query from your select. Assuming:

create table total_0122 ( x int, y int );
insert into total_0122 values (1,1);
create table total_0129 ( x int, y int );
insert into total_0122 values (2,2),(3,3);

Your query returns:

ColumnName  TableName
x   total_0122
y   total_0122
x   total_0129
y   total_0129

Now you can construct a new query like:

select coalesce(origin, 'Total'), count(x), count(y)
from (
  select 'total_0122' as origin, x, y from total_0122
  union
  select 'total_0129' as origin, x, y from total_0129
) as t
group by grouping sets ((origin),());

I.e. for each of the tables in your previos result, you create one leg in the union.

By using a grouping set with origin and the empty set, we get count's for individual tables, as well as for the total.

Fiddle

Answer 2

It sounds like you're looking for dynamic SQL, which can generate the INSERT statement for you. Then you can call sp_executesql to execute that INSERT statement, like so:

DROP TABLE IF EXISTS ##Totals;
SELECT TOP 0 email, country, revenue -- Hack to let SQL Server build the structure of the temp table for you 
INTO ##Totals
FROM total_0122;

DECLARE @DynamicSQL NVARCHAR(MAX) = N'';

SELECT @DynamicSQL = 
    STRING_AGG
    (
     CONCAT
     (
      'SELECT email, country, revenue
       FROM ', [name],
      'UNION ALL'
     ),
     CONCAT(CHAR(10), CHAR(13))
    )
FROM sys.tables
WHERE [name] LIKE '%total_%';

SET @DynamicSQL = CONCAT('INSERT INTO ##Totals', CONCAT(CHAR(10), CHAR(13)), @DyanmicSQL);

-- PRINT @DynamicSQL; -- For verifying correct syntax was generated

EXEC sp_executesql @DynamicSQL;

One caveat you'll notice is I'm hard-coding the column names and only using sys.tables to get me all of the tables. I figured this was ok since you'd only have to do it once, since all of the tables share the same columns. There is a way to have it automatically pull in the column names from sys.columns too, by using PIVOT but would be more verbose, probably more work than it's worth, and I'm typing this up on my phone, so this is where I tap out.

Also note the above uses STRING_AGG() which is only available on SQL Server 2017 or later (hopefully you're using a modern version, otherwise you'll need to use STUFF and FOR XML PATH which is just not fun). Best of luck!