List views columns in MySQL database

Query below lists all columns in views in MySQL database

Query

select col.table_schema as database_name,
       col.table_name as view_name,
       col.ordinal_position,
       col.column_name,
       col.data_type,
       case when col.character_maximum_length is not null
            then col.character_maximum_length
            else col.numeric_precision end as max_length,
       col.is_nullable
from information_schema.columns col
join information_schema.views vie on vie.table_schema = col.table_schema
                                  and vie.table_name = col.table_name
where col.table_schema not in ('sys','information_schema',
                               'mysql', 'performance_schema')
    -- and vie.table_schema = 'database_name' -- put your database name here
order by col.table_schema,
         col.table_name,
         col.ordinal_position;

Columns

  • database_name - database (schema) name
  • view_name - view name
  • column_name - column name
  • data_type - column datatype
  • max_length - column length:
    • For string columns, the maximum length in characters.
    • For numeric columns, the numeric precision.
  • is_nullable - flag indicating if column allows null value

Rows

  • One row represents one column in a specific view in a database
  • Scope of rows: all columns in views in MySQL database
  • Ordered by table schema, view name,

Sample results