Information Schema

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.

INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains. The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

Although you can select INFORMATION_SCHEMA as the default database with a  USE  statement, you can only read the contents of tables, not perform  INSERT ,  UPDATE , or  DELETE  operations on them.

Information Schema Table

Information Schema table provides information about tables in databases.

SELECT *
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

SELECT
    TABLE_NAME, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH,
    DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT,
    CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM,
    CREATE_OPTIONS, TABLE_COMMENT
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

SHOW TABLE STATUS
  FROM db_name
  [LIKE 'wild']

TABLE_ROWS represents the number of rows per table. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. This is important in case where you want to know how many rows or how much data a table has. For MyISAM, DATA_LENGTH is the length of the data file, in bytes. For InnoDB, DATA_LENGTH is the approximate amount of space allocated for the clustered index, in bytes. Specifically, it is the clustered index size, in pages, multiplied by the InnoDB page size.

SELECT 
    TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH,
    MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, CREATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'monomono';

// just row counts
SELECT 
    TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, 
    AUTO_INCREMENT, CREATE_TIME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'monomono';

Information Schema Event

Information Schema event provides information about events in databases.

SELECT
    EVENT_SCHEMA, EVENT_NAME, DEFINER, TIME_ZONE, EVENT_TYPE, EXECUTE_AT,
    INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS, STATUS, ORIGINATOR,
    CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION
  FROM INFORMATION_SCHEMA.EVENTS
  WHERE table_schema = 'db_name'
  [AND column_name LIKE 'wild']

SHOW EVENTS
  [FROM db_name]
  [LIKE 'wild']

Create an event

delimiter |

CREATE EVENT E_MIN_FIRE
    ON SCHEDULE
      EVERY 1 MINUTE
      STARTS CURRENT_TIMESTAMP
    COMMENT 'Saves total number of sessions then clears the table each day'
    DO
      BEGIN
        INSERT INTO activity(event_name, event_fired_at)
          VALUES('test-event',NOW());
      END |

delimiter ;

SELECT * 
FROM INFORMATION_SCHEMA.EVENTS
WHERE EVENT_SCHEMA = 'monomono';

SELECT 
    EVENT_SCHEMA, EVENT_NAME, CREATED, LAST_EXECUTED, DEFINER, TIME_ZONE, EVENT_DEFINITION,
    EVENT_TYPE, INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS, STATUS, ON_COMPLETION, EVENT_COMMENT
FROM INFORMATION_SCHEMA.EVENTS 
WHERE EVENT_SCHEMA = 'monomono';

Information Schema Trigger

Information schema trigger provides information about triggers.

SELECT * 
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='database_name';


// favorite fields
SELECT 
     TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_SCHEMA,
     EVENT_OBJECT_TABLE, ACTION_STATEMENT, ACTION_ORIENTATION, ACTION_TIMING, CREATED
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='database_name';

Information Schema ProcessList

The MySQL process list indicates the operations currently being performed by the set of threads executing within the server. 

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

SHOW FULL PROCESSLIST;

SHOW PROCESSLIST;

Get all column names per table per database

SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name = '{tableName}' AND table_schema = '{Database}'