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.