- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Given a column name how can I find which tables in a MySQL database contain that column?
Use the COLUMN_NAME to find which table in a database contains a specific column. Let us first create a table −
mysql> create table DemoTable -> ( -> CustomerId int, -> CustomerName varchar(20), -> CustomerCountryName varchar(100) -> ); Query OK, 0 rows affected (1.05 sec)
Following is the query to find in which tables a specific column “'CustomerCountryName'” is present −
mysql> select *from information_schema.columns WHERE COLUMN_NAME = 'CustomerCountryName';
Output
This will produce the following output −
+---------------+--------------+--------------+---------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+--------------+------------+-------+---------------------------------+----------------+-----------------------+-------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME |ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE |CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |NUMERIC_PRECISION | UMERIC_SCALE | DATETIME_PRECISION |CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | GENERATION_EXPRESSION | SRS_ID | +---------------+--------------+--------------+---------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+--------------+------------+-------+---------------------------------+----------------+-----------------------+-------+ | def | sample | demotable189 | CustomerCountryName | 4 | NULL |YES | varchar | 100 | 300 | NULL | NULL | NULL | utf8 | utf8_general_ci | varchar(100) | | |select,insert,update,references | | | NULL | | def | web | DemoTable | CustomerCountryName | 4 | NULL |YES | varchar | 20 | 60 | NULL | NULL |NULL | utf8 | utf8_unicode_ci | varchar(20) | | |select,insert,update,references | | | NULL | | def | web | DemoTable | CustomerCountryName | 3 | NULL |YES | varchar | 100 | 300 | NULL | NULL |NULL | utf8 | utf8_unicode_ci | varchar(100) | | |select,insert,update,references | | | NULL | +---------------+--------------+--------------+---------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+--------------+------------+-------+---------------------------------+----------------+-----------------------+-------+ 3 rows in set (0.68 sec)
Above, you can see the column “CustomerCountryName” is present in 3 tables.
- Related Articles
- How can I check MySQL tables from a database in accordance with particular column/s name?
- How to find tables with a specific column name in MySQL?
- How can I count unique records from a column in MySQL database?
- How can I query for all the tables having a particular column name?
- How can I check the character set of all the tables along with column names in a particular MySQL database?
- Find a specific column in all the tables in a database?
- Merging 2 tables with similar column name SAP HANA database
- How can I change the name of an existing column from a MySQL table?
- How Can I check the size of the tables in a particular MySQL database?
- How to find the name of a column in MySQL?
- How can I find and replace in MySQL in a column with file path?
- Get all the tables from a MySQL database having a specific column, let’s say xyz?
- How can I remove every column in a table in MySQL?
- How can I describe all tables in the database through a single statement in MySQL?
- Can I use InnoDB and MyISAM tables in a single database in MySQL?

Advertisements