MySQL query for getting all column names from all tables from a specific DB-CodePudding

I would need to find all %phone% instances from all tables in a specific DB.

This is my script i'm trying with yet still getting all the DBs (I need only from DB1):

use DB1;
SELECT  *
FROM
    information_schema.columns
WHERE
    column_name LIKE '%phone%';

I also tried writing like this:

SELECT  *
FROM
    DB1.information_schema.columns
WHERE
    column_name LIKE '%phone%';

but I got a SQL Error [1064] [42000] for that syntax.

What would be the correct way to query this?

CodePudding user response：

I used the following for '%email%' and it worked.

So i guess it would for for you too:

SELECT
    DISTINCT TABLE_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    COLUMN_NAME LIKE ('%phone%') AND TABLE_SCHEMA='DB1';

CodePudding user response：

There's only one INFORMATION_SCHEMA database for all the databases on the server. The table database is in the table_schema column of the columns table.

SELECT *
FROM information_schema.columns
WHERE table_schema = 'DB1' AND column_name LIKE '%phone%';

CodePudding user response：

use the parameter TABLE_SCHEMA, to select your scheam/database

SELECT  *
FROM
    information_schema.columns
WHERE
    column_name LIKE '%phone%'
    AND TABLe_SCHEMA = 'DB1';