By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tables
(
table_name sysname
);
insert into tables values ('table_1'), ('table_2');
create table table_1
(
Amount money,
Amt money
);
insert into table_1 values
(100.34, null),
(null, 12.34),
(1234.56, 78.90);
5 rows affected
create table table_2
(
col1 money,
col2 money,
col3 money
);
insert into table_2 values
(100, 200, 300),
(400, 500, 600);
2 rows affected
create table #result
(
table_name sysname,
column_name sysname,
total_sum money
)
declare @sql nvarchar(max);
declare @table sysname;
select @table = min(table_name) from tables
while @table is not null
begin
select @sql = 'with cte as (' + char(13)
+ 'select' + char(13)
+ string_agg(char(9) + quotename(column_name) + ' = sum(' + quotename(column_name) + ')', ',' + char(13)) + char(13)
+ 'from ' + max(quotename(table_name)) + char(13)
+ ')' + char(13)
+ 'select a.table_name, a.column_name, a.total_sum ' + char(13)
+ 'from cte ' + char(13)
+ 'cross apply (' + char(13)
+ char(9) + 'values' + char(13)
+ string_agg(char(9) + '(''' + table_name + ''', ''' + column_name + ''',' + quotename(column_name) + ')', ',' + char(13)) + char(13)
+ ') a (table_name, column_name, total_sum)' + char(13)
from information_schema.columns
where table_name = @table
and data_type = 'money'
insert into #result
exec sp_executesql @sql
select @table = min(table_name) from tables where table_name > @table
end
| table_name | column_name | total_sum |
|---|---|---|
| table_1 | Amount | 1334.9000 |
| table_1 | Amt | 91.2400 |
| table_2 | col1 | 500.0000 |
| table_2 | col2 | 700.0000 |
| table_2 | col3 | 900.0000 |
Warning: Null value is eliminated by an aggregate or other SET operation.