mysql,oracle,sqlServer 元数据查询

432次阅读
没有评论

最近在做多数据源的配置,需要根据数据库名和表(及视图)名获取表和字段的元数据;现对于各种查询语句,总结如下:

mysql元数据

  • 查询表及视图(表名,表/视图,表注释)
SELECT
    table_name,
    table_type,
    table_comment
FROM information_schema.TABLES
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'

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 不一定能查出来字段注释;

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
-- 7.16补充  这种可以查出来

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

查询视图,就不支持查询视图的字段及对应的字段注释了,因为他就是表的一个映射,一切都东西都在源表中有所体现,所以查表字段的不支持查视图,请知晓!呃,不知道后面会不会加hive的,要是加后面也会继续追加进来;奥利给!

viEcho
版权声明:本站原创文章,由viEcho2021-04-19发表,共计2416字。
转载提示:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
评论(没有评论)
载入中...