mysql,oracle,sqlServer 元数据查询

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

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

  • 下面此种可以查出来,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

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