mysql 表信息 类似于sqlserver中的sysobjects syscolumns sysproperties等

create view SystemInfo

as

SELECT table_name,ordinal_position as colorder,column_name as colName,

  case extra when 'auto_increment' then 1 else 0 end as IsIdentity,

  case column_key when 'pri' then 1 else 0 end as isKey,

  data_type as type,0 as bits,

  case  when character_maximum_length is NULL then numeric_precision else character_maximum_length end as length,

  numeric_scale as digit,

  case is_nullable when 'yes' then 0 else 1 end as isnullable,

  column_default as defaultValue,

  column_comment as memo,

  concat(table_name,'.',column_name) as col

FROM information_schema.COLUMNS ;