[SQL] Finding out all common columns in a set of tables

I have this table in SQL that contains a list of all tables and columns in these tables:

CREATE TABLE master_table (
table_name VARCHAR(50),
column_name VARCHAR(50)
);

INSERT INTO master_table (table_name, column_name) VALUES
('table1', 'col1'),
('table1', 'col2'),
('table1', 'col3'),
('table1', 'col4'),
('table2', 'col1'),
('table2', 'col3'),
('table2', 'col5'),
('table3', 'col1'),
('table3', 'col3'),
('table4', 'col1'),
('table5', 'col1');


I want to find out all common columns in these tables. The final result should look like this:

table_name col_name
table_1 col1
table_2 col1
table_3 col1
table_4 col1
table_5 col1


I tried doing this with a CTE approach:

WITH common_columns AS (
SELECT column_name
FROM master_table
GROUP BY column_name
HAVING COUNT(DISTINCT table_name) = (SELECT COUNT(DISTINCT table_name) FROM master_table)
)
SELECT m.table_name, m.column_name
FROM master_table m
JOIN common_columns c ON m.column_name = c.column_name
ORDER BY m.table_name, m.column_name;


But I am getting a NULL response

Can someone show me what I am doing wrong?

Continue reading...