SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 (col1 INTEGER, col2 INTEGER, col3 INTEGER, col4 INTEGER);
✓
CREATE TABLE table2 (col1 INTEGER, col2 INTEGER, col4 INTEGER);
✓
CREATE TABLE table3 (col1 INTEGER, col2 INTEGER, col3 INTEGER);
✓
INSERT INTO table1 VALUES (1, 2, 3, 4);
✓
INSERT INTO table2 VALUES (10, 20, 40);
✓
INSERT INTO table3 VALUES (100, 200, 300);
✓
WITH tables AS (SELECT name FROM sqlite_master WHERE type = 'table') 
SELECT pti.name col
FROM tables t CROSS JOIN pragma_table_info(t.name) pti
GROUP BY col
HAVING COUNT(*) = (SELECT COUNT(*) FROM tables);


col


col1

col2


WITH tables AS (SELECT name FROM sqlite_master WHERE type = 'table') 
SELECT GROUP_CONCAT(col) columns
FROM (
  SELECT pti.name col
  FROM tables t CROSS JOIN pragma_table_info(t.name) pti
  GROUP BY col
  HAVING COUNT(*) = (SELECT COUNT(*) FROM tables)
);


columns


col1,col2


WITH tables AS (SELECT name FROM sqlite_master WHERE type = 'table') 
SELECT GROUP_CONCAT(sql, ' UNION ALL ') sql
FROM ( 
  SELECT 'SELECT ' || 
    (
      SELECT GROUP_CONCAT(col) columns
      FROM (
        SELECT pti.name col
        FROM tables t CROSS JOIN pragma_table_info(t.name) pti
        GROUP BY col
        HAVING COUNT(*) = (SELECT COUNT(*) FROM tables)
      )
    ) || ' FROM ' || name AS sql
  FROM tables
);


sql


SELECT col1,col2 FROM table1 UNION ALL SELECT col1,col2 FROM table2 UNION ALL SELECT col1,col2 FROM table3


SELECT col1,col2 FROM table1 
UNION ALL 
SELECT col1,col2 FROM table2 
UNION ALL 
SELECT col1,col2 FROM table3


col1
col2


1
2

10
20

100
200