General table Cookbook

Showing all the fields of a table Deleting a table Showing all tables Creating a table Cloning a table Finding all tables with a specific column name Removing a temporary table Renaming a table Creating a temporary table Restoring a table from an sql file Backing up a table

thumb_up

thumb_down

chat_bubble_outline
0
 auto_stories new 
settings

Finding all tables with a specific column name in MySQL

Database

MySQL

Cookbooks

Tables Cookbooks

General table Cookbook

Jul 1, 2022

Last updated

local_offer MySQL

Tags

Table of Contents

Example

It is possible to check for all tables within a database that contain a specific column name using the below:


        
        
            
                
                
                    SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('specific_column1')
AND TABLE_SCHEMA='specific_database';

specific_column1 represents the column name you would like to check for.

specific_database represents the database that tables you would like to search for reside in.

Example

To locate all tables containing the column student_id within the people database:


        
        
            
                
                
                    SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('student_id')
AND TABLE_SCHEMA='people';
                
            
            +----------------------+
| TABLE_NAME           |
+----------------------+
| copy_drivers_license |
| drivers_license      |
| extracurricular      |
| new_drivers_license  |
| students             |
| students_copy        |
+----------------------+

Published by Arthur Yanagisawa

Edited by 0 others

Did you find this page useful?

thumb_up

thumb_down

Comment

Citation

Share on Twitter

Share on Facebook

Ask a question or leave a feedback...

thumb_up

thumb_down

chat_bubble_outline

settings

Enjoy our search

Hit / to insta-search docs and recipes!