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:

1. 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')

2. 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.

CodePudding user response：

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;

CodePudding user response：

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;