Search code examples
sqlmysql

Filter table with column name

I am working on a huge database. I want to find all the tables in all the database which contains columns name say "xyz" (cann't say actual column name). Is there any query or other way to solve the my problem. TIA

FYI DB : MySql

Solution

  • Sure, you can do that using INFORMATION_SCHEMA database:

    SELECT 
  TABLE_SCHEMA, 
  TABLE_NAME 
FROM 
  INFORMATION_SCHEMA.COLUMNS 
WHERE 
  COLUMN_NAME ='xyz';

    -for searching through all databases. If you want to do that only for your database, then add:

    SELECT 
  TABLE_NAME 
FROM 
  INFORMATION_SCHEMA.COLUMNS 
WHERE 
  COLUMN_NAME ='xyz' AND TABLE_SCHEMA='database';

    Of course, you can use LIKE comparison or any another thing (which is allowed in SQL) to filter your columns.