1、mysql 元数据查询

    • 查询表及视图（表名，表/视图，表注释）

      1. SELECT 
      2.   table_name,
      3.   table_type,
      4.   table_comment 
      5. FROM information_schema.TABLES 
      6. WHERE TABLE_SCHEMA = 'db_name'

    • 查询表字段（字段名，字段类型，字段注释）

      SELECT 
  column_name,
  DATA_TYPE as column_type,
  IF(COLUMN_COMMENT IS NULL OR COLUMN_COMMENT='', 
  COLUMN_NAME, COLUMN_COMMENT) AS column_comment
FROM information_schema.COLUMNS 
WHERE TABLE_NAME='table_name'

      2、oracle 元数据查询

    查询表及视图（表名，表/视图，表注释）

    select 
    do.object_name as table_name,
    do.object_type as table_type,
    utc.comments as table_remark
from dba_objects do
left join user_tab_comments utc 
on utc.table_name = do.object_name
where 
owner = 'db_name' 
and object_type in ('TABLE','VIEW')
    • 查询表字段（字段名，字段类型，字段注释）
      SELECT 
  DISTINCT(atc.column_name) as column_name,
  atc.data_type as column_type,
  ucc.comments as column_comment
FROM all_tab_cols atc
left join user_col_comments ucc 
on atc.column_name = ucc.column_name
where atc.table_name = 'TABLE_NAME'
and ucc.table_name = 'TABLE_NAME';
-- TABLE_NAME 大写哈，另外此sql 不一定能查出来字段注释；
-- 7.16补充  此种可以查出来
select 
    ut.COLUMN_NAME,--字段名称
    uc.comments,--字段注释
    ut.DATA_TYPE,--字典类型
    ut.DATA_LENGTH,--字典长度
    ut.NULLABLE--是否为空
from all_tab_columns  ut
inner JOIN all_col_comments uc
on ut.TABLE_NAME  = uc.table_name and ut.COLUMN_NAME = uc.column_name
where ut.TABLE_NAME='TABLE_NAME' 
order by ut.column_name
      3、SQLServer 元数据查询、
      select 
t1.table_name,
t1.table_type,
t2.table_remark from
  (select 
      sys.name as table_name,
      (CASE sys.xtype 
          WHEN 'U' 
          THEN 'TABLE'
          ELSE 'VIEW' END)as table_type
  from sysobjects sys where sys.xtype in ('U','V')) t1 
  left join
  (SELECT 
      CONVERT(nvarchar(50),ISNULL(A.[name], '')) as table_name,
      CONVERT(nvarchar(50),ISNULL(C.[value], '')) as table_remark
      FROM sys.tables A LEFT JOIN 
      sys.extended_properties C 
      ON C.major_id = A.object_id
  WHERE C.minor_id=0
) t2 on t1.table_name = t2.table_name
-- 此sql 不能查出视图注释，若你知道其他的办法可留言告知！
    • 查询表字段（字段名，字段类型，字段注释）
      select 
  t1.column_name,
  t2.column_type,
  t1.column_comment
from
(SELECT
  B.name as column_name,
  C.value as column_comment
  FROM sys.tables A
  INNER JOIN sys.columns B 
  ON B.object_id = A.object_id
  LEFT JOIN sys.extended_properties C 
  ON C.major_id = B.object_id 
  AND C.minor_id = B.column_id
  WHERE A.name = 'table_name')t1 
left join 
(select 
  column_name,
  data_type as column_type
from information_schema.columns t 
where t.table_name='table_name') t2
on t1.column_name = t2.column_name