mysql,oracle,sqlServer 元数据查询

最近在做多数据源的配置，需要根据数据库名和表（及视图）名获取表和字段的元数据；现对于各种查询语句，总结如下：

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

tips: 若你看到此处，了解oracle如何查出表字段注释的方式，还请在此篇博客下留言，谢谢！

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的，要是加后面也会继续追加进来；奥利给！