Oracle、MySql、PostgreSql获取数据库表的字段长度类型注释以及数据库表的注释

Oracle、MySql、PostgreSql获取数据库表的字段长度类型注释以及数据库表的注释

Oracle

1. 查询表结构

select
	u.table_name as "表名",
	u.column_name as "列名",
	u.comments as "列注释",
	us.data_type as "数据类型",
	us.data_length as "数据长度"
from
	user_col_comments u
	left join user_tab_columns us on u.table_name = us.table_name 
	and u.column_name = us.column_name 

2. 查询表的注释

SELECT
	table_name AS "表名",
	comments AS "表注释" 
FROM
	user_tab_comments

MySql

1. 查询表结构

SELECT
	TABLE_NAME AS "表名",
	COLUMN_NAME AS "列名",
	COLUMN_COMMENT AS "列注释",
	COLUMN_TYPE AS "数据类型" 
FROM
	information_schema.COLUMNS 
WHERE
	TABLE_SCHEMA = 'test' 
ORDER BY
	table_name DESC

2. 查询表注释

SELECT
	TABLE_NAME as "表名",
	TABLE_COMMENT as "表注释"
FROM
	information_schema.TABLES 
WHERE
	TABLE_SCHEMA = 'test';

TABLE_SCHEMA 就是指你要查的是哪个数据库的表的结构，不加，默认mysql所有的数据库的表结构都查出来。

PostgreSql

1. 查询表结构

SELECT
	info.TABLE_NAME AS "表名",
	info.COLUMN_NAME AS "列名",
	info.udt_name AS "数据类型",
	info.character_maximum_length AS "数据长度"
FROM
	information_schema.COLUMNS AS info 
WHERE
	info.table_schema = 'public' 
ORDER BY
	info.TABLE_NAME

SELECT C
	.relname AS "表名",
	A.attname AS "列名",
	D.description AS "列注释",
	T.typname AS "数据类型",
	( CASE WHEN A.attlen > 0 THEN A.attlen ELSE A.atttypmod - 4 END ) AS "数据长度" 
FROM
	pg_class C,
	pg_attribute A,
	pg_type T,
	pg_description D 
WHERE
	C.relname LIKE'job_%' 
	AND A.attnum > 0 
	AND A.attrelid = C.oid 
	AND A.atttypid = T.oid 
	AND D.objoid = A.attrelid 
	AND D.objsubid = A.attnum 
ORDER BY
	C.relname DESC,
	A.attnum ASC

2. 查询表注释

SELECT C
	.relname AS TABLE_NAME,
	d.description AS table_comment 
FROM
	pg_description d,
	pg_class C 
WHERE
	d.objoid = C.oid 
	AND objsubid = 0 

总结

都可以根据具体的需求进行加where条件进行查询或者模糊查询。