META-DATA queries in MYSQL

1.) display all the table name and tableschemas
select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE,TABLE_ROWS from information_Schema.tables;

2) displaying the table name and the column name
select TABLE_SCHEMA,TABLE_NAME, group_concat(COLUMN_NAME) colname,count(COLUMN_NAME) cnt  from information_schema.columns
where table_schema='test' group by TABLE_SCHEMA,TABLE_NAME ;
3) dislaying all the views and their structure .
select TABLE_SCHEMA,TABLE_NAME from information_schema.views where table_Schema like 'test'
select TABLE_SCHEMA,TABLE_NAME,VIEW_DEFINITION from information_schema.views where table_Schema like 'test' \G
4. display all the triggers and their structure
select TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_MANIPULATION,EVENT_OBJECT_TABLE,ACTION_Timing,ACTION_STATEMENT from information_Schema.triggers \G
 select TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_MANIPULATION,EVENT_OBJECT_TABLE,ACTION_Timing from information_Schema.triggers;
5. indexes
select table_schema,table_name,index_name,index_type,column_name from information_schema.STATISTICS where table_schema='test';
select table_schema,table_name,COLUMN_NAME,index_name,SEQ_IN_INDEX,NON_UNIQUE from information_schema.STATISTICS where table_schema='test';
select table_schema,table_name,COLUMN_NAME,group_concat(index_name),count(index_name) from information_schema.STATISTICS where table_schema='test' group by table_schema,table_name,COLUMN_NAME;
6.) routines
select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_BODY from information_Schema.routines;
select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_BODY,Routine_definition from information_Schema.routines;
7.) Primary key and their foreign  keys
SELECT CONCAT( table_name, '.', column_name, ' -> ', referenced_table_name, '.', referenced_column_name ) AS list_of_fks  FROM INFORMATION_SCHEMA.key_column_usage  WHERE referenced_table_schema = 'test' AND referenced_table_name IS NOT NULL  ORDER BY table_name, column_name;

8) count all the table and column count and record count
mysqlshow -u kiran -pkiran@123  test --count
mysqlshow -u kiran -pkiran@123  --count