MySQL Information_schema Use

--View table information for the specified database

SET @table_schema = ' Employees ' ; SELECT      table_name,    Table_type,    engine,    table_rows,    avg_row_length,    data_length,    index_length,    table_collation,    create_timefrom    information_ Schema.tablesWHERE = @table_schema ORDER      by TABLE_NAME;

--View Session connection information

SELECTthread_id, name, type, processlist_id, Processlist_user as User, Processlist_host asHost, processlist_db asdb, Processlist_command ascmd, Processlist_time asTime , Processlist_state asState , Processlist_info asinfo, Connection_type astype, thread_os_id asos_id fromperformance_schema.threadsWHEREtype= 'FOREGROUND'ORDER  bythread_id;

--Character_sets View the character set supported by the database

Information_schema. Character_setslike ' utf% 'CHARACTER  SETlike'utf%';

--Collations character Fu She

-- used to specify how datasets are sorted, and the alignment rules for strings SELECT *  from information_schema. CollationsWHERElike'utf%' like'  utf%';

--View table structure definition information

SELECT      table_name,    column_name,    ordinal_position,    data_type,    is_nullable,    column_default ,    Column_type,    column_key,    character_set_name,    collation_namefrom     Information_schema. COLUMNSWHERE = ' employees '             and = ' Employees ';

Show columns from employees to employees;

Desc employeees.employees;

--View the supported engines

SELECT *   from information_schema. Engines;show ENGINES;

--View data file information for a database

SELECT     file_id ,     file_name ,    File_type,    tablespace_name,    free_extents,    total_extents,    -*/  1024x768/1024x768 as mb_used,    extent_size,    initial_size ,    maximum_size,    autoextend_size,    data_free,    STATUS,    ENGINEfrom     information_schema. FILES;

--View constraints on the specified table

SELECTConstraint_schema, TABLE_NAME, constraint_name, COLUMN_NAME, Ordinal_position, CONCAT (table_name, '.', COLUMN_NAME,' -', Referenced_table_name,'.', Referenced_column_name) aslist_of_fks frominformation_schema. Key_column_usageWHEREReferenced_table_schema= 'Employees'         andReferenced_table_name is  not NULLORDER  byTABLE_NAME, COLUMN_NAME;

--View the specified partition table information

SELECT      Table_schema,    table_name,    partition_name,    subpartition_name sub_par,    partition_ordinal _position par_position,    Partition_method method,    partition_expression expression,    partition_ Description description,    table_rowsfrom    information_schema. PartitionsWHERE = ' test '             and = ' T ';

--View the supported plugins

SELECT   plugin_name, Plugin_status, Plugin_type,  plugin_library, Plugin_licensefrom  Information_schema. PLUGINS; SHOW PLUGINS;

--View Database connection information

SELECT *  from   full processlist;

--View stored procedures, functions, etc. in the database

SELECT      Routine_schema,    routine_name,    routine_type,    data_type,    routine_body,    routine_ Definition,    routine_commentfrom    information_schema. ROUTINESWHERE = ' PROCEDURE '      and Routine_schema="Employees";

--View the existing database and character set information

SELECT      schema_name,    default_character_set_name,    default_collation_namefrom     Information_schema. schemata; SHOW DATABASES;

--View index information

SELECT      Table_schema,    table_name,    index_name,    column_name,    COLLATION,    cardinality    , Index_typefrom    information_schema. STATISTICS WHERE  = ' employees '              and = ' Employees ';

SHOW INDEX from employees from employees;

--View database size

SELECTTable_schema'Database', CONCAT (ROUND(SUM(Data_length+Index_length)/(1024x768 * 1024x768),                    2),            'M') Size frominformation_schema. TABLESWHEREENGINE=('MyISAM' || 'InnoDB')GROUP  byTable_schema;

--View Table size

SELECTCONCAT (Table_schema,'.', TABLE_NAME) table_name, CONCAT (ROUND(Data_length/(1024x768 * 1024x768),2),            'M') Data_length, CONCAT (ROUND(Index_length/(1024x768 * 1024x768),2),            'M') Index_length, CONCAT (ROUND(ROUND(Data_length+Index_length)/(1024x768 * 1024x768),                    2),            'M') Total_size frominformation_schema. TABLESORDER  byData_lengthDESC;

MySQL Information_schema Use