mysql 表名和字段、备注

 

select
     t1.table_schema
    ,t1.table_name
    ,t1.table_comment
    ,t2.ordinal_position
    ,t2.column_name
    ,t2.data_type
    ,t2.character_maximum_length
    ,t2.column_type
    ,replace(replace(t2.column_comment,'\r',','),'\n',',') as column_comment
from (
    select *
    from information_schema.tables
    where table_schema = 'db_name'
        and table_type = 'BASE TABLE'
        and table_name like '%fk%'
) t1
left join (
    select *
    from information_schema.columns
    where table_schema = 'db_name'
        and table_name like '%fk%'
) t2
    on t1.table_schema = t2.table_schema and t1.table_name = t2.table_name
order by
     t1.table_schema
    ,t1.table_name
    ,t2.ordinal_position
;

 

 

select
     column_name
    ,data_type
    ,CHARACTER_MAXIMUM_LENGTH
    ,column_type
    ,column_comment
from information_schema.columns
where TABLE_SCHEMA ='db_name'
    and TABLE_NAME = 'v_name'
order by ORDINAL_POSITION
;