Tuesday, September 22, 2020

How to find all the tables in MySQL with specific column names in them?

How to find all the tables in MySQL with specific column names in them?

🔥 Save unlimited web pages along with a full PDF snapshot of each page.
Unlock Premium →

I have 2-3 different column names that I want to look up in the entire DB and list out all tables which have those columns. Any easy script?

To get all tables with columns columnA or ColumnB in the database YourDatabase:

SELECT DISTINCT TABLE_NAME       FROM INFORMATION_SCHEMA.COLUMNS      WHERE COLUMN_NAME IN ('columnA','ColumnB')          AND TABLE_SCHEMA='YourDatabase';
SELECT TABLE_NAME, COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS  WHERE COLUMN_NAME LIKE '%wild%';

More simply done in one line of SQL:

SELECT * FROM information_schema.columns WHERE column_name = 'column_name';
SELECT DISTINCT TABLE_NAME, COLUMN_NAME    FROM INFORMATION_SCHEMA.COLUMNS    WHERE column_name LIKE 'employee%'    AND TABLE_SCHEMA='YourDatabase'

In version that do not have information_schema (older versions, or some ndb's) you can dump the table structure and search the column manually.

mysqldump -h$host -u$user -p$pass --compact --no-data --all-databases > some_file.sql

Now search the column name in some_file.sql using your preferred text editor, or use some nifty awk scripts.


And a simple sed script to find the column, just replace COLUMN_NAME with your's:

sed -n '/^USE/{h};/^CREATE/{H;x;s/\nCREATE.*\n/\n/;x};/COLUMN_NAME/{x;p};' <some_file.sql  USE `DATABASE_NAME`;  CREATE TABLE `TABLE_NAME` (    `COLUMN_NAME` varchar(10) NOT NULL,

You can pipe the dump directly in sed but that's trivial.

For those searching for the inverse of this, i.e. looking for tables that do not contain a certain column name, here is the query...

SELECT DISTINCT TABLE_NAME FROM information_schema.columns WHERE   TABLE_SCHEMA = 'your_db_name' AND TABLE_NAME NOT IN (SELECT DISTINCT   TABLE_NAME FROM information_schema.columns WHERE column_name =   'column_name' AND TABLE_SCHEMA = 'your_db_name');

This came in really handy when we began to slowly implement use of InnoDB's special ai_col column and needed to figure out which of our 200 tables had yet to be upgraded.

If you want "To get all tables only", Then use this query:

SELECT TABLE_NAME   FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_NAME like '%'  and TABLE_SCHEMA = 'tresbu_lk'

If you want "To get all tables with Columns", Then use this query:

SELECT DISTINCT TABLE_NAME, COLUMN_NAME    FROM INFORMATION_SCHEMA.COLUMNS    WHERE column_name LIKE '%'    AND TABLE_SCHEMA='tresbu_lk'

Use this one line query, replace desired_column_name by your column name.

SELECT TABLE_NAME FROM information_schema.columns WHERE column_name = 'desired_column_name';
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%city_id%' AND TABLE_SCHEMA='database'

The problem with information_schema is that it can be terribly slow. It is faster to use the SHOW commands.

After you select the database you first send the query SHOW TABLES. And then you do SHOW COLUMNS for each of the tables.

In PHP that would look something like

    $res = mysqli_query("SHOW TABLES");      while($row = mysqli_fetch_array($res))      {   $rs2 = mysqli_query("SHOW COLUMNS FROM ".$row[0]);          while($rw2 = mysqli_fetch_array($rs2))          {   if($rw2[0] == $target)                 ....          }      }    
select distinct table_name   from information_schema.columns   where column_name in ('ColumnA')   and table_schema='YourDatabase';  and table_name in   (   select distinct table_name    from information_schema.columns    where column_name in ('ColumnB')   and table_schema='YourDatabase';  );

That ^^ will get the tables with ColumnA AND ColumnB instead of ColumnA OR ColumnB like the accepted answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.

Source: https://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them
This web page was saved on Tuesday, Sep 22 2020.

Upgrade to Premium Plan

✔ Save unlimited bookmarks.

✔ Get a complete PDF copy of each web page

✔ Save PDFs, DOCX files, images and Excel sheets as email attachments.

✔ Get priority support and access to latest features.

Upgrade now →

No comments: