4

I am somewhat of a novice to SQL, but have been playing around with it using SQLFiddle to get better at it. What if you wanted to find and list all the tables that have common columns or columns that are shared across the different tables (e.g. table 1, 2, 3, & 4 have a customer field, while table 2 & 4 have a student field? How would you do that with MySQL? Lets say you have 100+ tables and not 2.

E.g.

Table 1:

ID | Customer | Pet |

Table 2:

ID | Customer | Food | Student |

Table 3:

ID | Customer | Activity |

Table 4:

ID | Customer | Cost | Student

Expected Outcome:

Table_NAME | COLUMN_NAME

Table 1, Table 2, Table 3, Table 4 | ID

Table 1, Table 2, Table 3, Table 4 | CUSTOMER

Table 2, Table 4 | Student

I tried both the below and did not give me what I was looking for:

select Table_NAME, COLUMN_NAME, Count(*)
from INFORMATION_SCHEMA.columns
GROUP BY Table_NAME
HAVING COUNT(*) > 1


SELECT Table_Name, Column_Name
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME IN
(     SELECT TABLE_NAME
      FROM INFORMATION_SCHEMA.columns
      GROUP BY TABLE_NAME
      HAVING COUNT(*) > 1
 )
 ORDER BY TABLE_NAME
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
Dwayne
  • 41
  • 2
  • If you are new to SQL, here comes the general GROUP BY rule: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function. (MySQL does things different here...) – jarlh Sep 28 '15 at 13:40
  • @jarlh For MySql it actually depends on the mysql version. Prior to 5.3.4(not sure if this is the one though) it allows you to do a group by without full columns of the select (which I always think it is a mess) see this answer: http://stackoverflow.com/a/1646121/460557 – Jorge Campos Sep 28 '15 at 13:45

1 Answers1

2

Try:

select GROUP_CONCAT(TABLE_NAME) TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
GROUP BY COLUMN_NAME
HAVING COUNT(*) > 1

Above query will give tables/column information from all schema in your database.

If you want to limit the data to a particular schema then;

select GROUP_CONCAT(TABLE_NAME) TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'your_schema'
GROUP BY COLUMN_NAME
HAVING COUNT(*) > 1
Praveen
  • 8,945
  • 4
  • 31
  • 49
  • Brilliant. Wow this worked like magic. Few questions for you... 1) How comes I can't do Select CONCAT (TABLE_NAME,', ', TABLE_NAME) as oppose to doing GROUP_CONCAT(Table_NAME) Table_Name? 2) When you do GROUP_CONCAT, how can you add a delimiter like , followed by space? – Dwayne Sep 28 '15 at 21:12