Compare two table and find matching columns

Question

Hi,

I have two tables table1 and table2, i need to write a select query which will list me the columns that exist in both the tables.(mysql)

I need to do for different tables (2 at a time)

Is this possible?

I tried using INFORMATION_SCHEMA.COLUMNS but am not able to get it right.

Answer 1

Yes this is possible, just use MySQL's SHOW COLUMNS syntax to get the columns from each table in an array, then use PHP's array_intersect() function to find the columns in both.

Answer 2

 SELECT a.COLUMN_NAME
 FROM INFORMATION_SCHEMA.COLUMNS a
 JOIN INFORMATION_SCHEMA.COLUMNS b
 ON a.COLUMN_NAME = b.COLUMN_NAME
 AND b.TABLE_NAME = 'table2'
 AND b.TABLE_SCHEMA = database() //or manually enter it
 WHERE a.TABLE_NAME = 'table1'
 AND a.TABLE_SCHEMA = database(); //or manually enter it