데이터베이스 질문과 답변

테이블 조회시 컬렴명대신 코멘트가 나오게 하는법 0 2 4,816

by NEOCAN [SQL Query] SELECT 컬럼 COMMENT 물리명 논리명 [2016.12.19 21:43:48]

테이블 조회시 컬럼명 대신에 코멘트가 나오게 하는 방법을 아시나요

예를 들어 

 

 SELECT STD_ID

         , SBJ_SCR

  FROM SCHOOL

 

이런 쿼리가 있다고 했을때 STD_ID, SBJ_SCR 이라는 컬렴의 물리명이 아닌 논리명(COMMENT)가 나오게 하는 쿼리문이나 방법을 아시는 분은 알려줏세요!!!

by 거제도원주민 [2016.12.20 00:32:47]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
아래 쿼리를 활용하시면 comment사항을 활용하실수 있을겁니다.
여러가지로 활용가능한 쿼리입니다. 참조하세요...
테이블 owner명과, 테이블명을 입력하시고...  그리고 comment 사항이 잘 정리되어 있다면
활용가능하리라 생각됩니다.
 
         SELECT DECODE( 'U',                                    
                        'L', Lower(ALLTAB.COLUMN_NAME),                
                        'U', Upper(ALLTAB.COLUMN_NAME),                
                              CASE WHEN INSTR(ALLTAB.COLUMN_NAME, '_') > 0 THEN            
                                       Lower(SUBSTR(ALLTAB.COLUMN_NAME,1,INSTR(ALLTAB.COLUMN_NAME, '_') -1)) || REPLACE(INITCAP(REPLACE(SUBSTR(Lower(ALLTAB.COLUMN_NAME), INSTR(ALLTAB.COLUMN_NAME, '_')), '_', ' ')),' ','') 
                                  ELSE LOWER(ALLTAB.COLUMN_NAME)    
                              END) COLUMN_NAME,                                                        
                RPAD(( CASE                                        
                                WHEN ALLTAB.DATA_TYPE = 'NUMBER'             THEN ALLTAB.DATA_TYPE || DECODE(NVL( ALLTAB.DATA_PRECISION, 0 ), 0, '', '(' || ALLTAB.DATA_PRECISION || ',' ||  ALLTAB.DATA_SCALE || ')')                         
                                WHEN ALLTAB.DATA_TYPE = 'CHAR'               THEN 'VARCHAR2(' || ALLTAB.CHAR_COL_DECL_LENGTH || ')'                        
                                WHEN ALLTAB.DATA_TYPE IN ('DATE','DATETIME') THEN ALLTAB.DATA_TYPE || ALLTAB.CHAR_COL_DECL_LENGTH
                                WHEN ALLTAB.DATA_TYPE IN ('CLOB', 'BLOB')    THEN ALLTAB.DATA_TYPE
                                WHEN ALLTAB.DATA_TYPE IN ('LONG') THEN ALLTAB.DATA_TYPE
                                 ELSE ALLTAB.DATA_TYPE || '(' || ALLTAB.CHAR_COL_DECL_LENGTH || ')'                        
                          END ), 15, ' ' ) DATA_TYPE,                                
                RPAD(( CASE                                        
                                WHEN ALLTAB.NULLABLE = 'N'  THEN 'NN'                         
                                WHEN ALLTAB.NULLABLE = 'Y'  THEN '  '                        
                          END ), 15, ' ' )  NULL_INDC,                                
                RPAD(( SELECT NVL(TABLESPACE_NAME, 'TS_XXXXXXXXXX') TABLESPACE_NAME                                       
                            FROM SYS.ALL_TABLES                            
                           WHERE TABLE_NAME = ALLTAB.TABLE_NAME                                
                                AND OWNER         = ALLTAB.OWNER), 15, ' ' ) TBL_SPACE,                                                   
                ALLTAB.DATA_DEFAULT DFLT_VALUE,                                               
                ALLCOL.COMMENTS,                                         
               ALLTAB.COLUMN_ID ,
               NVL((SELECT DECODE(SUBSTR(INDEX_NAME,1,2),'PK', 'Y', 'N')
                  FROM ALL_IND_COLUMNS
                 WHERE TABLE_OWNER = ALLTAB.OWNER
                   AND TABLE_NAME = ALLTAB.TABLE_NAME
                   AND COLUMN_NAME = ALLTAB.COLUMN_NAME
                   AND INDEX_NAME LIKE 'PK%'), 'N') PK_INDC
         FROM SYS.ALL_TAB_COLUMNS ALLTAB,                                               
                SYS.ALL_COL_COMMENTS ALLCOL
      WHERE ALLTAB.OWNER = 'SNCNSONE'                          -----------------> owner명
          AND ALLTAB.TABLE_NAME = 'RA010M'                     ------------------> 테이블명   
          AND ALLTAB.OWNER        = ALLCOL.OWNER               
          AND ALLTAB.TABLE_NAME = ALLCOL.TABLE_NAME                                                
          AND ALLTAB.COLUMN_NAME= ALLCOL.COLUMN_NAME

 

by 우리집아찌 [2016.12.20 09:09:30]
컬럼명 대신 코맨트가 나온다고요??
다이나믹 쿼리로 처리하셔야 할텐대 .. 널값 들어있고나 하면 문제 될텐데요
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입