- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- 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
Find a specific column in all the tables in a database?
For this, use COLUMN_NAME and set LIKE with that specific column name. Let us find a specific column in an unknown table in a database −
mysql> SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE column_name LIKE '%StudentName%' -> AND table_schema = 'web';
Output
This will produce the following output −
+-------------------+-------------+-----------+-------------+----------------+ | TABLE_NAME | COLUMN_NAME | DATA_TYPE | IS_NULLABLE |COLUMN_DEFAULT | +-------------------+-------------+-----------+-------------+----------------+ | demotable215 | StudentName | varchar | YES | NULL | | demotable221 | StudentName | varchar | YES | NULL | | demotable224 | StudentName | varchar | YES | NULL | | demotable234 | StudentName | varchar | YES | NULL | | demotable269 | StudentName | varchar | YES | NULL | | DemoTable | StudentName | varchar | YES | NULL | | DemoTable | StudentName | varchar | YES | NULL | | DemoTable | StudentName | varchar | YES | NULL | | DemoTable | StudentName | varchar | NO | NULL | | DemoTable | StudentName | varchar | YES | NULL | | DemoTable | StudentName | json | YES | NULL | | DemoTable | StudentName | varchar | YES | NULL | | DemoTable | StudentName | varchar | YES | NULL | | DemoTable | StudentName | varchar | YES | NULL | | DemoTable | StudentName | varchar | NO | NULL | | DemoTable | StudentName | varchar | YES | NULL | | DemoTable | StudentName | varchar | YES | NULL | | DemoTable | StudentName | varchar | YES | NULL | | view_DemoTable | StudentName | varchar | YES | NULL | +-------------------+-------------+-----------+-------------+----------------+ 19 rows in set (0.07 sec)
Above displays all the tables with a specific column “StudentName”.
Advertisements