¿Cómo encontrar todas las tablas en MySQL con nombres de columna específicos en ellas?

Tengo 2-3 nombres de columna diferentes que quiero buscar en todo el DB y enlistar todas las tablas que tienen esas columnas. ¿Algún guión fácil?

Para obtener todas las tablas con columnas columnA o ColumnB en la base de datos 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%'; 

Más simplemente hecho en una línea de 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' 

En la versión que no tiene information_schema (versiones anteriores, o algunos ndb) puede volcar la estructura de la tabla y buscar la columna manualmente.

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

Ahora busque el nombre de la columna en some_file.sql usando su editor de texto preferido, o use algunas ingeniosas secuencias de comandos awk.


Y un simple script sed para encontrar la columna, simplemente reemplace COLUMN_NAME con el suyo:

 sed -n '/^USE/{h};/^CREATE/{H;x;s/\nCREATE.*\n/\n/;x};/COLUMN_NAME/{x;p};'  

Puede canalizar el volcado directamente en sed, pero eso es trivial.

Para aquellos que buscan el inverso de esto, es decir, que buscan tablas que no contienen un cierto nombre de columna, aquí está la consulta …

 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'); 

Esto fue muy útil cuando comenzamos a implementar lentamente la columna especial ai_col de InnoDB y necesitábamos averiguar cuál de nuestras 200 tablas aún no se había actualizado.

Si desea ” Para obtener todas las tablas solamente “, utilice esta consulta:

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

Si desea ” Obtener todas las tablas con columnas “, utilice esta consulta:

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

Utilice esta consulta de una línea, reemplace nombre_columna_acabado por su nombre de columna.

 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'