The view information_schema.tables contains information about all tables and views in the database.
SELECT * FROM information_schema.tables;
SELECT table_schema, table_name, table_type FROM information_schema.tables WHERE NOT is_system;
information_schema.tables
| column name | type | remarks |
|---|---|---|
| table_catalog | VARCHAR | Always NULL |
| table_schema | VARCHAR | |
| table_name | VARCHAR | |
| table_type | VARCHAR | |
| self_referencing_column_name | VARCHAR | Always NULL |
| reference_generation | VARCHAR | Always NULL |
| user_defined_type_catalog | VARCHAR | Always NULL |
| user_defined_type_schema | VARCHAR | Always NULL |
| user_defined_type_name | VARCHAR | Always NULL |
| is_insertable_into | VARCHAR | NO or YES |
| is_typed | VARCHAR | NO or YES |
| commit_action | VARCHAR | DELETE or PRESERVE or DROP or NULL when not applicable |
| schema_id | INTEGER | reference to sys.schemas.id |
| table_id | INTEGER | reference to sys.tables.id |
| table_type_id | SMALLINT | reference to sys.table_types.table_type_id |
| row_count | BIGINT | Approximate number of rows in the table. Populated only for persistent tables. |
| is_system | BOOLEAN | true when it is a system table or view, else false |
| is_view | BOOLEAN | true when it is a view, else false |
| query_def | VARCHAR | contents of sys.tables.query |
| comments | VARCHAR |
Note: The last 8 columns (schema_id, table_id, table_type_id, row_count, is_system, is_view, query_def and comments) are extensions to the view as defined by ISO standard. They provide useful information and simplify filtering and joins with system tables/views in sys schema when needed.