How to find ONLY system tables in MySQL database?

Question

I am trying to find the list of MySQL's system tables (or internal tables). The database "mysql" contains both its system tables (internal or proprietary to MySQL database) and user created tables.

For example, by executing the below query

 SELECT table_name, table_type, engine, TABLE_COMMENT 
   FROM information_schema.tables 
   Where TABLE_SCHEMA = 'mysql' 
   order by engine desc; 

we get all tables' type as BASE TABLE only. These entries include my user table also (for example, CREATE TABLE mysql.tst1 (i INT) ENGINE = MYISAM;)

Could you please let me know how can we differentiate between internal(or system) tables AND user created tables? Do we have any query or system objects like stored procedure ....etc ...?

Answer 1

how about the below query i guess you missed out the information_schemaand performance_schema

so check out the below query

SELECT table_name, table_type, engine, TABLE_COMMENT 
   FROM information_schema.tables 
Where 
   TABLE_SCHEMA IN ('mysql','information_schema','performance_schema')
ORDER BY engine DESC