테이블 컬럼상세 조회하기

**************************************************************
* 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

 

/**************************************************************
* 테이블 별로 어떤 PK를 가지고 있는지 보자
***************************************************************/
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