SQL 查询表字段信息

SQL 查询表字段信息

一、MySQL

（一）查询所有表名

查询指定数据库下表名

select table_name,table_comment from information_schema.tables where table_schema='grandly_forum_db'

（二）查询表下所有字段

查询指定数据库下表的字段信息

select a.table_schema,a.table_name,b.table_comment 表说明,a.COLUMN_NAME 字段名,a.column_comment 字段说明,a.column_type 字段类型,a.column_key 约束 frominformation_schema.columns ajoin information_schema.TABLES b on a.table_name = b.TABLE_NAMEwhere a.table_schema = '数据库名'and a.table_name = '表名';

二、Oracle

（一）查询表下所有字段

SELECTDISTINCTc.owner 用户,c.table_name 英文表名 ,t.comments 中文表名称,cc.column_name 字段名,cc.comments 字段注释 ,c.data_type || '(' || c.data_length || ')' 字段类型及长度fromdba_tab_columns cjoin dba_col_comments cc on c.table_name = cc.table_nameand c.column_name = cc.column_namejoin dba_tab_comments t on c.table_name = t.table_namewhere c.owner = 'YZZX'-- AND t.comments like '%船舶信息%'AND c.table_name ='SHX_SHIPCERT'order BYcc.column_name 

（二）查询库下表字段信息

with t1 as(SELECT t.OWNER owner_name, t.table_name, t.column_name, f.comments, t.data_type, t.data_lengthFROM all_tab_columns t JOIN all_col_comments f ON t.table_name = f.table_name AND t.column_name = f.column_nameORDER BY t.table_name,t.column_name),t2 as (select col.column_name pk,col.table_namefrom user_constraints con,user_cons_columns colwherecon.constraint_name=col.constraint_name and con.constraint_type='P')select distinct t1.owner_name 用户名,t1.table_name 表名,t2.pk 主键,t1.column_name 字段名,t1.comments 字段注释,t1.data_type 数据类型,t1.data_length 长度from t1join t2 on t1.table_name = t2.table_namewhere t1.owner_name = 'ZHZFHZK' and t1.comments is not nullorder by t1.table_name,column_name

（三）查询用户下表的外建等信息

 select distinctc.owner 用户名, CONSTRAINT_TYPE 键类型, c.TABLE_NAME 子表名, c.CONSTRAINT_NAME 子表键名称, d.TABLE_NAME 父表名称, c.R_CONSTRAINT_NAME 父表键名称, d.COLUMN_NAME 字段名称 from all_constraints c join all_cons_columns d on c.constraint_type = 'R' and c.R_CONSTRAINT_NAME = d.CONSTRAINT_NAME where c.owner = 'SCOTT' ;

（四）oracle查询用户下表外建信息

SELECTt1.table_name AS table_name,t2.table_name AS f_table_name,t1.column_name,t2.column_name as f_column_nameFROMall_constraints consJOIN all_cons_columns col1 ON cons.owner = col1.owner AND cons.constraint_name = col1.constraint_nameJOIN all_tab_columns t1 ON cons.owner = t1.owner AND col1.table_name = t1.table_name AND col1.column_name = t1.column_nameJOIN all_cons_columns col2 ON cons.owner = col2.owner AND cons.r_owner = col2.owner AND cons.r_constraint_name = col2.constraint_nameJOIN all_tab_columns t2 ON cons.r_owner = t2.owner AND col2.table_name = t2.table_name AND col2.column_name = t2.column_nameWHERE cons.constraint_type = 'R' AND t1.owner = 'WANGLIN'ORDER BY t1.table_name;

关注林哥，持续更新哦！！！★,°:.☆(￣▽￣)/$:.°★ 。