各数据获取设计表信息 字段 注释 长度等系统信息(不定期更新)

• MySQL

获取到database_name数据库中的表

select table_name,column_name,is_nullable,data_type,character_maximum_length,column_comment from information_schema.columns where table_schema = 'tabale_name'

获取database_name数据库中table_name表的表信息

select  column_name,is_nullable,data_type,character_maximum_length,column_comment from information_schema.columns where table_schema = 'database_name' AND table_name = 'table_name' 

• Postgresql

获取到database_name数据库中的表

select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0;

获取数据库中table_name表的表信息 

select NAME,attnotnull,TYPE,case when type = 'timestamp(0) without time zone'  then 'nan'  when position = 0 then 'nan' else   substr(type,position('(' in type)+1,char_length(type) - position('(' in type) -1 ) end as length,COMMENT from (
 SELECT
 A.attname AS NAME,
 A.attnotnull AS attnotnull,
 format_type ( A.atttypid, A.atttypmod ) AS TYPE,
 col_description ( A.attrelid, A.attnum ) AS COMMENT,
 position('(' in format_type ( A.atttypid, A.atttypmod ))
 FROM
 pg_class AS C,
 pg_attribute AS A 
 WHERE
 C.relname = 'table_name' 
 AND A.attrelid = C.oid 
 AND A.attnum > 0)a

 

• Oracle

获取到该 用户下/所有用户 的表

select * from user_tab_comments  -- 用户下所有的表
select * from all_tab_comments -- 所有用户的表

获取到表/视图字段注释信息

select * from user_col_comments  -- 获取到用户下所有表的字段和注释
select * from all_tab_comments -- 获取到所有用户下所有表的字段和注释

获取到表是/视图字段详细信息(不包括注释信息可以和上表关联)

select * from user_tab_columns  -- 查询本用户的表字段详细信息
select * from all_tab_columns  -- 查询所有用户的表字段详细信息

获取tabale_name表的信息

select a.COLUMN_NAME,a.NULLABLE,a.DATA_TYPE,a.DATA_LENGTH,b.comments 
from dba_tab_columns a,user_col_comments b 
where a.column_name=b.column_name
and a.table_name = b.table_name
and a.table_name = 'table_name'