꿈꾸는 개발자, DBA 커뮤니티 구루비

오라클 테이블 정의서?? 테이블 스키마 출력하는 쿼리좀 도와주세요

by 도와주십시요 ㅠ,ㅜ [2013.12.22 14:08:00]

테이블명	한글 테이블명	컬럼 ID	컬럼명	한글 컬럼명	TYPE	길이	PK	FK
TB_AA01	AA관련테이블	1	aaaa2	aaaa1컬럼	number	4,2	O	O
TB_AA02	AA관련테이블	2	aaaa1	aaaa2컬럼	char	1	X	O
TB_AA02	AA관련테이블	3	aaaa4	aaaa4컬럼	varchar	2	X	X

위와 같은 형태로 owner 가 같은 것들의 DB스키마를 뽑을려고 하는데 잘안되네요 ㅠㅠ
FK 를 같이 추가 시키면 컬럼이 늘어나면서  총 로우 숫자도 안맞고 돌것네요 정확해야 하는데 ㅠㅠ
누가 잘만들어진 쿼리좀 올려주시거나 수정좀 해주시면 감사하겠습니다.

SELECT '' as EntityName, com.COMMENTS , col.TABLE_NAME, col.COLUMN_NAME, col.DATA_TYPE, col.DATA_LENGTH,
    decode(col.NULLABLE,'N','NN','') as NN, decode(con.CONSTRAINT_TYPE,'P','PK','') as Keys, com.COMMENTS , '' as Remark
   FROM ALL_TAB_COLUMNS col, ALL_COL_COMMENTS com,
    (
    SELECT A.TABLE_NAME, B.COLUMN_NAME, A.CONSTRAINT_NAME, A.CONSTRAINT_TYPE, A.SEARCH_CONDITION
    FROM ALL_CONSTRAINTS A, ALL_CONS_COLUMNS B
    WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
    AND A.CONSTRAINT_TYPE = 'P'
    )
    con
WHERE
col.TABLE_NAME=com.TABLE_NAME and
col.COLUMN_NAME=com.COLUMN_NAME and
col.TABLE_NAME=con.TABLE_NAME(+) and
col.COLUMN_NAME=con.COLUMN_NAME(+) and
col.OWNER='테이블 오너'
order by col.TABLE_NAME asc, col.COLUMN_ID asc

by 빈이 [2013.12.23 10:13:32]

이거 응용해보세요..

SELECT
    A.TABLE_NAME
    , (SELECT COMMENTS FROM DBA_TAB_COMMENTS E WHERE E.TABLE_NAME = A.TABLE_NAME AND E.OWNER = B.OWNER) AS TABLE_COMMENTS
    , A.COLUMN_NAME
    , A.COLUMN_ID
    , A.DATA_TYPE||'('||A.DATA_LENGTH||')' AS DATA_LENGTH
    , B.COMMENTS
    , (SELECT D.CONSTRAINT_NAME FROM USER_CONSTRAINTS C, USER_CONS_COLUMNS D
    WHERE A.TABLE_NAME = C.TABLE_NAME
    AND C.CONSTRAINT_NAME = D.CONSTRAINT_NAME
    AND D.COLUMN_NAME = A.COLUMN_NAME
    AND C.CONSTRAINT_TYPE = 'P'
    AND C.OWNER = B.OWNER
    AND D.OWNER = B.OWNER
    ) AS PK_NAME
FROM COLS A, DBA_COL_COMMENTS B
WHERE 1=1
AND A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
AND B.OWNER = 'OWNER'

by 부쉬맨 [2013.12.23 11:03:15]

SELECT X.column_id "Pos",
    X.column_name "Column",
    X.position "PK",
    X.data_type_mod "Ref",
    X.data_type_owner "Type Owner",
    X.data_type "Type",
    X.nullable "Null?",
    Y.data_default "Default",
    X.comments "Comments"
FROM (
select MIN(column_id) column_id,
    column_name,
    MIN(position) position,
    MIN(data_type_mod) data_type_mod,
    MIN(data_type_owner) data_type_owner,
    MIN(data_type) data_type,
    MIN(nullable) nullable,
    MIN(comments) comments
FROM (
select t.column_id,
    t.column_name,
    TO_NUMBER(NULL) position,
    t.data_type_mod,
    t.data_type_owner,
    t.data_type ||
      decode(data_type,'DATE','','CHAR',' ('||t.char_length||decode(CHAR_USED,'B',' Byte','C',' Char')||')','VARCHAR2',' ('||t.char_length||decode(CHAR_USED,'B',' Byte','C',' Char')||')',
       'NCHAR',' ('||t.char_length||decode(CHAR_USED,'B',' Byte','C',' Char')||')','VARCHAR',' ('||t.char_length||decode(CHAR_USED,'B',' Byte','C',' Char')||')',
       'NVARCHAR2',' ('||t.char_length||decode(CHAR_USED,'B',' Byte','C',' Char')||')','RAW',' ('||t.data_length||')',
       'NUMBER',Decode(data_scale,NULL, decode(data_precision,NULL,NULL,' ('||data_precision||')')
           ,0,decode(data_precision,NULL,' (38)',' ('||data_precision||')')
           ,' ('||data_precision||','||data_scale||')'),
       'FLOAT',Decode(data_scale,NULL,
           decode(data_precision,NULL,NULL,' ('||data_precision||')')
           ,0,' ('||data_precision||')'
           ,' ('||data_precision||','||data_scale||')')) data_type,
    DECODE(t.nullable,'Y','','N') nullable,
    c.comments
from  sys.dba_tab_columns t, sys.dba_col_comments c
where t.owner = :p_owner
and  t.table_name = :p_tab
and  t.owner = c.owner
and  t.table_name = c.table_name
and  t.column_name = c.column_name
UNION ALL
select TO_NUMBER(NULL),
    c.column_name, c.position, NULL, NULL, NULL, NULL, NULL
from  sys.dba_constraints p, sys.dba_cons_columns c
 where p.owner = :p_owner
 and p.table_name = :p_tab
 and p.constraint_type = 'P'
 and p.owner = c.owner
 and p.table_name = c.table_name
 and p.constraint_name = c.constraint_name
)
GROUP BY COLUMN_NAME
) X, sys.dba_tab_columns Y
WHERE X.COLUMN_NAME = Y.COLUMN_NAME
AND  Y.owner = :p_owner
AND  Y.table_name = :p_tab
order by X.column_id

by 사랑초 [2013.12.23 11:22:28]

음.. PK, FK 에 대한 정의가 명확하지 않으신 것 같아서..

대략적인 것만 올려드리니 수정해서 사용하시면 좋을 것 같습니다^^;;

SELECT A.TABLE_NAME,

B.COMMENTS AS "TBL_COMMENT",

A.TABLESPACE_NAME,

C.COLUMN_ID AS "COL_NO",

C.COLUMN_NAME AS "COL_NAME",

CASE

WHEN C.DATA_TYPE = 'VARCHAR2' OR C.DATA_TYPE = 'CHAR'

THEN

C.DATA_TYPE || '(' || DATA_LENGTH || ')'

WHEN C.DATA_TYPE = 'NUMBER'

AND DATA_PRECISION > 0

AND DATA_SCALE > 0

THEN

C.DATA_TYPE

|| '('

|| DATA_PRECISION

|| ','

|| DATA_SCALE

|| ')'

WHEN C.DATA_TYPE = 'NUMBER' AND DATA_PRECISION > 0

THEN

C.DATA_TYPE || '(' || DATA_PRECISION || ')'

WHEN C.DATA_TYPE = 'NUMBER'

THEN

C.DATA_TYPE || '()'

ELSE

C.DATA_TYPE

END

AS "DATA_TYPE",

E.KEY1,

E.KEY2,

DECODE (NULLABLE, 'N', 'No', 'Yes') AS "NULL",

DATA_DEFAULT "DEFAULT",

D.COMMENTS "COL_COMMENT"

FROM USER_TABLES A,

USER_TAB_COMMENTS B,

USER_TAB_COLUMNS C,

USER_COL_COMMENTS D,

(

SELECT

A.TABLE_NAME

, A.COLUMN_NAME

, DECODE (B.CONSTRAINT_TYPE, 'P', 'PRIMARY KEY', 'U', 'UNIQUE KEY') AS KEY1

, DECODE (B.CONSTRAINT_TYPE, 'R', 'FOREIGN KEY') AS KEY2

FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B

WHERE A.TABLE_NAME = B.TABLE_NAME

AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

AND B.CONSTRAINT_TYPE IN ('P','U','R')

) E

WHERE A.TABLE_NAME = B.TABLE_NAME

AND A.TABLE_NAME = C.TABLE_NAME

AND C.TABLE_NAME = D.TABLE_NAME

AND C.COLUMN_NAME = D.COLUMN_NAME

AND C.TABLE_NAME = E.TABLE_NAME(+)

AND C.COLUMN_NAME = E.COLUMN_NAME(+)

;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

Rich Text Editor, commentnote

Editor toolbars

Press ALT 0 for help

◢Elements path

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변