MySQL - Information Schema - Referências

Para pesquisar informações sobre Tabelas, Colunas, Procedures, etc do MySQL pode-se usar as diversas tabelas do schema padrão INFORMATION_SCHEMA.

São elas:

• CHARACTER_SETS
• COLLATIONS
• COLLATION_CHARACTER_SET_APPLICABILITY
• COLUMNS
• COLUMN_PRIVILEGES
• ENGINES
• EVENTS
• FILES
• GLOBAL_STATUS
• GLOBAL_VARIABLES
• KEY_COLUMN_USAGE
• PARTITIONS
• PLUGINS
• PROCESSLIST
• PROFILING
• REFERENTIAL_CONSTRAINTS
• ROUTINES
• SCHEMATA
• SCHEMA_PRIVILEGES
• SESSION_STATUS
• SESSION_VARIABLES
• STATISTICS
• TABLES
• TABLE_CONSTRAINTS
• TABLE_PRIVILEGES
• TRIGGERS
• USER_PRIVILEGES
• VIEWS

Exemplos:

1) Para ver todas as tabelas de um determinado Schema:

SELECT t1.table_name
  FROM information_schema.tables t1

  WHERE lower(t1.table_schema) = 'information_schema';

2) Identificar quais tabelas referenciam uma determinada coluna:

SELECT t1.*
  FROM information_schema.columns t1
  WHERE upper(t1.table_schema) = 'information_schema'

    AND upper(t1.column_name) = 'COLUMN_NAME';