How to get all columns that are used from various tables?

Lets say I have 10 tables (Table1 - Table10) and 10 columns in each table (lets say Column1 - Column10) - same column names across tables. I wanna find all columns in all tables that are not NULL for at least one row in that table and insert them into temp table for example @usedColumns that will be used later in the stored procedure.

What would be the fastest and most efficient way to do it assuming that you have millions of records in any or all of these tables? Current implementation uses UNPIVOT, but I'm trying to redesign that stored procedure since it's acting really slow. Or is UNPIVOT really the most efficient way?

One way I was thinking is to just check for if exists, but can't figure out how to put this into an efficient loop:

if exists (select top 1 1 from Table1 where Column1 is not null)
    insert into @usedColumns(table_name, column_name)
        values('Table1', 'Column1');

if exists (select top 1 1 from Table1 where Column2 is not null)
    insert into @usedColumns(table_name, column_name)
        values('Table1', 'Column2');
...

The DB in question is SQL Server, but I'm guessing the solution would work in MySQL and others too.