How can we lookup column names of one table with data in another table in mysql

Question

If any one can help me

I have scenario were I need to take the col name of one table (there is no data only the col name) and then search those col name in the data available in another table .

I can manually do it like take a col name and then search in another table using like and I see it exist. But can some help me know how we can write a SQL script for this.

Answer 1

SQL :

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'TableName'

MYSQL

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' 
    AND `TABLE_NAME`='yourtablename';

Answer 2

if you are looking to compare the entire database it could work like this, you can restrict it to single table as well

        SELECT * FROM
        (SELECT 
                    c.TABLE_SCHEMA_S,
                    c.TABLE_SCHEMA_D,
                    c.TABLE_NAME_S,
                    c.TABLE_NAME_D,
                    GROUP_CONCAT(c.COLUMN_NAME)  COLUMN_NAMES
            FROM
                (SELECT DISTINCT
                    a.TABLE_SCHEMA TABLE_SCHEMA_S,
                    b.TABLE_SCHEMA TABLE_SCHEMA_D,
                    a.TABLE_NAME TABLE_NAME_S,
                    b.TABLE_NAME TABLE_NAME_D,
                    b.COLUMN_NAME
            FROM
                information_schema.COLUMNS a
            INNER JOIN information_schema.COLUMNS b ON a.TABLE_NAME = b.TABLE_NAME
                AND a.COLUMN_NAME = b.COLUMN_NAME
            WHERE
                a.TABLE_SCHEMA = 'first-db-name'
                    AND b.TABLE_SCHEMA = 'second-db-name') c
            GROUP BY c.TABLE_NAME_S) d;