Oracle数据字典（用于做代码生成工具用）

/**************************************************************
* User Table 表名、注释、字段类型、字段长度、是否为空、等
***************************************************************/
SELECT COL.TABLE_NAME as TNAME,  
TCOM.COMMENTS as TCMT,
COL.COLUMN_NAME as COL_NM,  
CCOM.COMMENTS as COL_CMT,  
COL.DATA_TYPE as TYPE_CD,  
DECODE(COL.DATA_TYPE, 'NUMBER',COL.DATA_PRECISION ||'.'||COL.DATA_SCALE,    COL.DATA_LENGTH) as LENGTH,
COL.NULLABLE as NULL_YN,
COL.DATA_DEFAULT as D_DEFAULT,
COL.NUM_DISTINCT as NUM_DISTINCT
FROM USER_TAB_COLUMNS COL,
USER_TAB_COMMENTS TCOM,
USER_COL_COMMENTS CCOM
WHERE COL.TABLE_NAME  = TCOM.TABLE_NAME AND
COL.TABLE_NAME  = CCOM.TABLE_NAME AND
COL.COLUMN_NAME = CCOM.COLUMN_NAME AND
COL.TABLE_NAME  = 'ITEM_CLASS'
ORDER BY COL.TABLE_NAME ,
    COL.COLUMN_ID  



--> PK 数据库主键
SELECT COL.TABLE_NAME as TNAME,  
TCOM.COMMENTS as TCMT,
COL.COLUMN_NAME as COL_NM,  
CCOM.COMMENTS as COL_CMT,
COL.COLUMN_ID ID,
PKCOL.COLUMN_POSITION AS PK,  
COL.DATA_TYPE as TYPE_CD,  
DECODE(COL.DATA_TYPE, 'NUMBER',COL.DATA_PRECISION ||'.'||COL.DATA_SCALE,    COL.DATA_LENGTH) as LENGTH,
COL.NULLABLE as NULL_YN,
COL.DATA_DEFAULT as D_DEFAULT,
COL.NUM_DISTINCT as NUM_DISTINCT
FROM USER_TAB_COLUMNS COL,
USER_TAB_COMMENTS TCOM,
USER_COL_COMMENTS CCOM,
( SELECT  AA.TABLE_NAME, AA.INDEX_NAME, AA.COLUMN_NAME, AA.COLUMN_POSITION
  FROM   USER_IND_COLUMNS AA, USER_CONSTRAINTS BB
  WHERE   BB.CONSTRAINT_TYPE = 'P'
  AND AA.TABLE_NAME = BB.TABLE_NAME
  AND AA.INDEX_NAME = BB.CONSTRAINT_NAME
--  AND AA.TABLE_NAME IN ('ITEM_CLASS')  -- 이것 풀면 좀더 빠름
) PKCOL
WHERE COL.TABLE_NAME  = TCOM.TABLE_NAME
AND COL.TABLE_NAME  = CCOM.TABLE_NAME
AND COL.COLUMN_NAME = CCOM.COLUMN_NAME
AND COL.TABLE_NAME  = 'ITEM_CLASS'
AND COL.COLUMN_NAME = PKCOL.COLUMN_NAME(+)
AND COL.TABLE_NAME  = PKCOL.TABLE_NAME(+)
ORDER BY COL.TABLE_NAME ,
    COL.COLUMN_ID 



/**************************************************************
* 能区别字段是否是主键的
***************************************************************/
  SELECT USER_TAB_COLUMNS.TABLE_NAME as TNAME,  
         USER_TAB_COMMENTS.COMMENTS as TCMT,
   USER_TAB_COLUMNS.COLUMN_NAME as COL_NM,  
   USER_COL_COMMENTS.COMMENTS as COL_CMT,  
         USER_TAB_COLUMNS.DATA_TYPE as TYPE_CD,  
         DECODE(USER_TAB_COLUMNS.DATA_TYPE, 'NUMBER',USER_TAB_COLUMNS.DATA_PRECISION ||'.'||USER_TAB_COLUMNS.DATA_SCALE,    USER_TAB_COLUMNS.DATA_LENGTH) as LENGTH,
         USER_TAB_COLUMNS.NULLABLE as NULL_YN
    FROM USER_TAB_COLUMNS,
  USER_TAB_COMMENTS,
  USER_COL_COMMENTS
WHERE USER_TAB_COLUMNS.TABLE_NAME  = USER_TAB_COMMENTS.TABLE_NAME
AND USER_TAB_COLUMNS.TABLE_NAME  = USER_COL_COMMENTS.TABLE_NAME
AND USER_TAB_COLUMNS.COLUMN_NAME = USER_COL_COMMENTS.COLUMN_NAME
AND USER_TAB_COMMENTS.TABLE_TYPE = 'TABLE' AND ( USER_TAB_COLUMNS.COLUMN_NAME  = 'ITEM_CODE'
  OR USER_TAB_COLUMNS.COLUMN_NAME  = 'ITEM_FROM_DATE' )
ORDER BY USER_TAB_COLUMNS.TABLE_NAME ,
  USER_TAB_COLUMNS.COLUMN_ID  



--> 表名，最大的，最小
select tname, '',  MAX(col1), MAX(col2)
from (
  SELECT USER_TAB_COLUMNS.TABLE_NAME as TNAME,  
   decode(USER_TAB_COLUMNS.COLUMN_NAME,'ITEM_CODE', USER_TAB_COLUMNS.COLUMN_NAME, '') COL1,
   decode(USER_TAB_COLUMNS.COLUMN_NAME,'ITEM_FROM_DATE', USER_TAB_COLUMNS.COLUMN_NAME, '') COL2
    FROM USER_TAB_COLUMNS,
  USER_TAB_COMMENTS,
  USER_COL_COMMENTS
WHERE USER_TAB_COLUMNS.TABLE_NAME  = USER_TAB_COMMENTS.TABLE_NAME
AND USER_TAB_COLUMNS.TABLE_NAME  = USER_COL_COMMENTS.TABLE_NAME
AND USER_TAB_COLUMNS.COLUMN_NAME = USER_COL_COMMENTS.COLUMN_NAME
AND USER_TAB_COMMENTS.TABLE_TYPE = 'TABLE'
AND ( USER_TAB_COLUMNS.COLUMN_NAME  = 'ITEM_CODE'
  OR USER_TAB_COLUMNS.COLUMN_NAME  = 'ITEM_FROM_DATE' )
ORDER BY USER_TAB_COLUMNS.TABLE_NAME ,
         USER_TAB_COLUMNS.COLUMN_ID  
)
group by tname



/**************************************************************
*
***************************************************************/
SELECT AA.TABLE_NAME, AA.INDEX_NAME, AA.COLUMN_NAME, AA.COLUMN_POSITION
FROM   SYS.DBA_IND_COLUMNS AA, SYS.DBA_CONSTRAINTS BB
WHERE  AA.TABLE_OWNER = 'IMPORT'
--AND    AA.TABLE_NAME IN ('ITEM_CLASS')
AND BB.CONSTRAINT_TYPE = 'P'
AND AA.TABLE_OWNER = BB.OWNER
AND AA.TABLE_NAME = BB.TABLE_NAME
AND AA.INDEX_NAME = BB.CONSTRAINT_NAME
ORDER BY AA.TABLE_NAME, AA.COLUMN_POSITION