List all column names that are non-unique across tables

Question

I have a need to know which column names are used on more than one table of my database.

I've got this query as a start, which gives me a frequency table of column names across the database:

SELECT COLUMN_NAME, count(COLUMN_NAME) count
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='dbName'
GROUP BY COLUMN_NAME
ORDER BY count DESC;

However, I'd like the query to also do the following:

Limit to only those records where count>1 (I tried adding AND count>1 to the WHERE clause, but I got the error Error Code: 1054. Unknown column 'count' in 'where clause')
Show the names of all the tables on which each column appears (this could entail multiple rows per column name, one for each table name). If it's easier, I can leave off the count column, as long as condition 1 is met.

Note: This is similar to this question, but I don't want to search for a specific column; I want to find ALL column names that are repeated.

Ergest Basha · Accepted Answer · 2022-09-29T18:21:35.380

I have a need to know which column names are used on more than one table of my database

This will give which tables are used more than once

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='dbName'
GROUP BY COLUMN_NAME
HAVING COUNT(COLUMN_NAME) >1 ;

If you want to know how many the columns are repeated and which one is repeated the most use:

SELECT COLUMN_NAME,
       COUNT(COLUMN_NAME) AS col_cnt
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='dbName'
GROUP BY COLUMN_NAME
HAVING COUNT(COLUMN_NAME) >1 
ORDER BY col_cnt DESC;

Edit. I missed the second requirements , so you could use below query to find the table names as well:

SELECT i.COLUMN_NAME,
       i.TABLE_NAME,
       col_cnt
FROM INFORMATION_SCHEMA.COLUMNS i
INNER JOIN (SELECT COLUMN_NAME,
                   COUNT(COLUMN_NAME) AS col_cnt
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA='dbname'
            GROUP BY COLUMN_NAME
            HAVING COUNT(COLUMN_NAME) >1 
             ) AS tbl ON i.COLUMN_NAME=tbl.COLUMN_NAME
ORDER BY col_cnt DESC;

Brilliant. [`HAVING`](https://stackoverflow.com/a/16156003) is the key word I was missing. This accomplishes my condition 1, and based on this I figured out a query that also satisfies condition 2. — Aaron Dunigan AtLee, Sep 29 '22 at 17:43
@AaronDuniganAtLee I missed the second condition, so I added an answer for that — Ergest Basha, Sep 29 '22 at 18:22

score 0 · Answer 2 · answered Sep 29 '22 at 17:55

Based on Ergest Basha's answer I came up with the following query, which meets both conditions (lists the non-unique columns AND shows which tables they appear on):

SELECT I.COLUMN_NAME, TABLE_NAME, F.freq
FROM INFORMATION_SCHEMA.COLUMNS I
LEFT JOIN (
  SELECT COLUMN_NAME, COUNT(COLUMN_NAME) freq
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA='dbName'
  GROUP BY COLUMN_NAME
) F
ON I.COLUMN_NAME=F.COLUMN_NAME
WHERE TABLE_SCHEMA='dbName' 
AND F.freq>1
ORDER BY COLUMN_NAME;

List all column names that are non-unique across tables

2 Answers2