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.