The view information_schema.columns contains information about all columns of tables and views in the database.
SELECT * FROM information_schema.columns;
SELECT table_schema, table_name, column_name, ordinal_position FROM information_schema.columns;
information_schema.columns
| column name | type | remarks |
|---|---|---|
| table_catalog | VARCHAR | Always NULL |
| table_schema | VARCHAR | |
| table_name | VARCHAR | |
| column_name | VARCHAR | |
| ordinal_position | INTEGER | |
| column_default | VARCHAR | |
| is_nullable | VARCHAR | NO or YES |
| data_type | VARCHAR | |
| character_maximum_length | INTEGER | |
| character_octet_length | BIGINT | |
| numeric_precision | INTEGER | |
| numeric_precision_radix | INTEGER | |
| numeric_scale | INTEGER | |
| datetime_precision | INTEGER | |
| interval_type | VARCHAR | |
| interval_precision | INTEGER | |
| character_set_catalog | VARCHAR | Always NULL |
| character_set_schema | VARCHAR | Always NULL |
| character_set_name | VARCHAR | |
| collation_catalog | VARCHAR | Always NULL |
| collation_schema | VARCHAR | Always NULL |
| collation_name | VARCHAR | Always NULL |
| domain_catalog | VARCHAR | Always NULL |
| domain_schema | VARCHAR | Always NULL |
| domain_name | VARCHAR | Always NULL |
| udt_catalog | VARCHAR | Always NULL |
| udt_schema | VARCHAR | Always NULL |
| udt_name | VARCHAR | Always NULL |
| scope_catalog | VARCHAR | Always NULL |
| scope_schema | VARCHAR | Always NULL |
| scope_name | VARCHAR | Always NULL |
| maximum_cardinality | INTEGER | |
| dtd_identifier | VARCHAR | Always NULL |
| is_self_referencing | VARCHAR | NO or YES |
| is_identity | VARCHAR | NO or YES |
| identity_generation | VARCHAR | NO or YES |
| identity_start | BIGINT | |
| identity_increment | BIGINT | |
| identity_maximum | BIGINT | |
| identity_minimum | BIGINT | |
| identity_cycle | VARCHAR | |
| is_generated | VARCHAR | NO or YES |
| generation_expression | VARCHAR | |
| is_system_time_period_start | VARCHAR | |
| is_system_time_period_end | VARCHAR | |
| system_time_period_timestamp_generation | VARCHAR | |
| is_updatable | VARCHAR | NO or YES |
| declared_data_type | VARCHAR | Always NULL |
| declared_numeric_precision | INTEGER | |
| declared_numeric_scale | INTEGER | |
| schema_id | INTEGER | reference to sys.schemas.id |
| table_id | INTEGER | reference to sys.tables.id |
| column_id | INTEGER | reference to sys.columns.id |
| sequence_id | INTEGER | reference to sys.sequences.id when is_identity = 'YES' |
| is_system | BOOLEAN | true when the column is part of a system table or view, else false |
| comments | VARCHAR |
Note: The last 6 columns (schema_id, table_id, column_id, sequence_id, is_system 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.