Cómo buscar tablas por nombre de columna en MySQL y MariaDB

Hace unos días, trabajando en la refactorización de la base de datos de nuestro ERP en DOSCLIC, me encontré con la necesidad de localizar rápidamente todas las tablas que contenían la columna company_id. Intentar algo intuitivo como un SHOW TABLES WHERE... no funciona directamente en MySQL o MariaDB porque esa instrucción trabaja estrictamente a nivel de nombres de tablas, pero la solución elegante reside en explotar el potencial de information_schema.

1. El límite de SHOW TABLES y la alternativa real

Cuando intentas buscar columnas directamente con comandos de inspección superficial, te chocas contra la pared de la sintaxis. Para buscar de verdad en la estructura de tus tablas, debes recurrir a la base de datos virtual information_schema, específicamente a la tabla COLUMNS. Esta tabla almacena los metadatos de todas las columnas de todas las bases de datos del servidor.

La consulta básica para encontrar todas las tablas que tengan una columna llamada company_id dentro de la base de datos activa es la siguiente:

SELECT TABLE_NAME, COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND COLUMN_NAME LIKE '%company_id%';

2. ¿Qué es realmente TABLE_SCHEMA?

En el ecosistema de MySQL y MariaDB, TABLE_SCHEMA es el término técnico para referirse al nombre de la base de datos. A diferencia de otros motores como PostgreSQL donde un esquema y una base de datos son entidades jerárquicas distintas, aquí son prácticamente sinónimos.

Si prefieres hacer una búsqueda explícita apuntando directamente a tu base de datos de producción (por ejemplo, erp), puedes definir el valor de forma estática:

SELECT TABLE_NAME, COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'erp'
  AND COLUMN_NAME LIKE '%company_id%';

Si lo único que necesitas es el listado único de tablas, evitando duplicados en caso de que existan múltiples columnas que coincidan con el patrón, simplemente añade un DISTINCT:

SELECT DISTINCT TABLE_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'erp'
  AND COLUMN_NAME LIKE '%company_id%';

3. Auditoría estructural y detección de inconsistencias

Buscar tablas por nombre de columna no solo sirve para ubicarse en un desarrollo nuevo; es una herramienta de auditoría brutal. Uno de los problemas más comunes en bases de datos que han crecido orgánicamente es la inconsistencia de tipos de datos en las claves foráneas (por ejemplo, que companies.id sea BIGINT pero users.company_id sea INT).

Podemos extender la consulta para extraer detalles críticos de la estructura de la columna:

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    COLUMN_TYPE,
    COLUMN_KEY
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'erp'
  AND COLUMN_NAME LIKE '%company_id%'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Con este reporte, puedes identificar inmediatamente si alguna tabla tiene configurada la columna de relación de una forma distinta, lo que podría romper la integridad referencial o degradar el rendimiento de los índices.

4. Comparación de estructuras entre ambientes (Schema Diff)

Otra de las aplicaciones más potentes de TABLE_SCHEMA es comparar la estructura de dos bases de datos distintas en el mismo servidor (por ejemplo, tu base de datos de producción erp y la de pruebas erp_test).

Para encontrar qué columnas tienen tipos de datos diferentes entre ambos ambientes, puedes ejecutar este JOIN sobre la misma tabla de metadatos:

SELECT
    a.TABLE_NAME,
    a.COLUMN_NAME,
    a.COLUMN_TYPE AS ERP_TYPE,
    b.COLUMN_TYPE AS TEST_TYPE
FROM information_schema.COLUMNS a
JOIN information_schema.COLUMNS b
    ON b.TABLE_SCHEMA = 'erp_test'
   AND b.TABLE_NAME = a.TABLE_NAME
   AND b.COLUMN_NAME = a.COLUMN_NAME
WHERE a.TABLE_SCHEMA = 'erp'
  AND a.COLUMN_TYPE <> b.COLUMN_TYPE
ORDER BY a.TABLE_NAME, a.COLUMN_NAME;

5. Buscando relaciones reales (Foreign Keys)

Es vital recordar que el hecho de que una columna se llame company_id no garantiza que exista una relación a nivel de motor de base de datos; podría ser simplemente una convención de nombres a nivel de software. Para verificar relaciones reales y restricciones de clave foránea, debemos consultar KEY_COLUMN_USAGE:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_SCHEMA,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'erp'
  AND COLUMN_NAME = 'company_id'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

6. Generación automática de comandos SQL

Una de las mayores ventajas de usar information_schema es que puedes utilizar los resultados para construir sentencias SQL dinámicas. Por ejemplo, si necesitas generar rápidamente consultas de conteo para todas las tablas que contienen la columna company_id, puedes hacer que MySQL escriba el código por ti:

SELECT CONCAT(
    'SELECT \'', TABLE_NAME, '\' AS tabla, COUNT(*) AS total FROM `',
    TABLE_SCHEMA, '`.`',
    TABLE_NAME,
    '`;'
) AS sql_command
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'erp'
  AND COLUMN_NAME = 'company_id';

Al ejecutar esta consulta, obtendrás un listado de comandos listos para copiar y ejecutar en tu terminal, automatizando una tarea que de otro modo te habría tomado varios minutos de escritura manual.

7. Chuleta rápida de metadatos

Para facilitar tus tareas de administración y auditoría en el día a día, aquí tienes una tabla de referencia rápida con los componentes clave de information_schema: