我从USER_TAB_COLS中取内容，从USER_COL_COMMENTS中取备注可以得到下面的表结构
SELECT
USER_TAB_COLS.COLUMN_ID AS 列序号 ,
USER_TAB_COLS.COLUMN_NAME AS 列名 ,
USER_TAB_COLS.DATA_TYPE AS 数据类型,
USER_TAB_COLS.DATA_LENGTH AS 长度,
USER_TAB_COLS.NULLABLE AS 是否可空,
USER_COL_COMMENTS.COMMENTS AS 备注
FROM USER_TAB_COLS
INNER JOIN USER_COL_COMMENTS ON
USER_COL_COMMENTS.TABLE_NAME = USER_TAB_COLS.TABLE_NAME
AND USER_COL_COMMENTS.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME
WHERE USER_TAB_COLS.TABLE_NAME = ‘AI_HFSC_ZH_CHK‘ ORDER BY 列序号
但是如果用plsql查询该表的表结构，可以发现不一样的状况
如果出错，那一定是我的语句找错了，那么，我应该怎么改这个语句，让我得到的数据与plsql提供的表结构数据一致呢？
select A.COLUMN_ID as 列序号, A.COLUMN_NAME as 列名, case when A.DATA_TYPE = ‘CHAR‘ then A.DATA_TYPE || ‘(‘ || A.DATA_LENGTH || ‘)‘ when A.DATA_TYPE = ‘VARCHAR2‘ then A.DATA_TYPE || ‘(‘ || A.DATA_LENGTH || ‘)‘ when A.DATA_TYPE = ‘DATE‘ then A.DATA_TYPE when A.DATA_TYPE = ‘NUMBER‘ and A.DATA_PRECISION IS NULL AND a.DATA_SCALE = 0 then ‘INTEGER‘ when A.DATA_TYPE = ‘NUMBER‘ AND a.DATA_SCALE = 0 then A.DATA_TYPE || ‘(‘ || A.DATA_PRECISION || ‘)‘ when A.DATA_TYPE = ‘NUMBER‘ AND a.DATA_SCALE != 0 then A.DATA_TYPE || ‘(‘ || A.DATA_PRECISION || ‘,‘ || A.DATA_SCALE || ‘)‘ END as 数据类型, decode(C.COLUMN_NAME, null, ‘‘, ‘√‘) as 主键, decode(A.NULLABLE, ‘Y‘, ‘√‘) as 可为空, B.comments as 备注 from sys.user_tab_cols A, sys.user_col_comments B, (select col.column_name, c.table_name, col.owner from user_constraints c, user_cons_columns col where c.constraint_name = col.constraint_name and c.constraint_type = ‘P‘) C where upper(A.TABLE_NAME) = ‘AI_HFSC_ZH_CHK‘ and A.TABLE_NAME = B.table_name and A.COLUMN_NAME = B.column_name and A.Table_Name = C.TABLE_NAME(+) and A.COLUMN_NAME = C.COLUMN_NAME(+) order by A.TABLE_NAME, A.Column_Id
如果说查询当前User所有的外键
--查询外键约束的列名： select ucc.constraint_name,ucc.table_name,ucc.column_name from user_cons_columns ucc left join user_constraints uc on uc.constraint_name=ucc.constraint_name where uc.constraint_type=‘R‘ ;
然后加入对于外键的查询
-----------解决了外键的问题，但时 数据类型、备注 又出问题了 select A.COLUMN_ID as 列序号, A.COLUMN_NAME as 列名, case when A.DATA_TYPE = ‘CHAR‘ then A.DATA_TYPE || ‘(‘ || A.DATA_LENGTH || ‘)‘ when A.DATA_TYPE = ‘VARCHAR2‘ then A.DATA_TYPE || ‘(‘ || A.DATA_LENGTH || ‘)‘ when A.DATA_TYPE = ‘DATE‘ then A.DATA_TYPE when A.DATA_TYPE = ‘NUMBER‘ and A.DATA_PRECISION IS NULL AND a.DATA_SCALE = 0 then ‘INTEGER‘ when A.DATA_TYPE = ‘NUMBER‘ AND a.DATA_SCALE = 0 then A.DATA_TYPE || ‘(‘ || A.DATA_PRECISION || ‘)‘ when A.DATA_TYPE = ‘NUMBER‘ AND a.DATA_SCALE != 0 then A.DATA_TYPE || ‘(‘ || A.DATA_PRECISION || ‘,‘ || A.DATA_SCALE || ‘)‘ END as 数据类型, decode(C.COLUMN_NAME, null, ‘‘, ‘√‘) as 主键, decode(d.constraint_name,null, ‘‘, ‘√‘) as 外键, decode(A.NULLABLE, ‘Y‘, ‘√‘) as 可为空, B.comments as 备注 from sys.user_tab_cols A, sys.user_col_comments B, (select col.column_name, c.table_name, col.owner from user_constraints c, user_cons_columns col where c.constraint_name = col.constraint_name and c.constraint_type = ‘P‘) C, (select ucc.constraint_name,ucc.table_name,ucc.column_name from user_cons_columns ucc left join user_constraints uc on uc.constraint_name=ucc.constraint_name where uc.constraint_type=‘R‘) D where A.TABLE_NAME = ‘ACT_RU_JOB‘ and A.TABLE_NAME = B.table_name and A.COLUMN_NAME = B.column_name and A.Table_Name = C.TABLE_NAME(+) and A.COLUMN_NAME = C.COLUMN_NAME(+) and a.TABLE_NAME=d.table_name(+) and a.COLUMN_NAME=d.column_name(+) order by A.TABLE_NAME, A.Column_Id
应该是什么地方出错了
原文：https://www.cnblogs.com/adamgq/p/12213758.html