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 不一定能查出来字段注释；
-- 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的，要是加后面也会继续追加进来；奥利给！