728x90
반응형
#
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 45 46 47 48 49 | -- 테이블정보 SELECT A.CLASS_NAME AS 테이블명 , A.COMMENT AS [한글테이블명] , B.ATTR_NAME AS 컬럼영문명 , B.COMMENT AS 한글컬럼명 , B.IS_NULLABLE AS [NULL여부] , B.DATA_TYPE AS 데이터타입 , B.PREC AS 데이터길이 , CASE WHEN D.KEY_ATTR_NAME IS NOT NULL THEN 'Y' END AS PK정보 , CASE WHEN F.KEY_ATTR_NAME IS NOT NULL THEN 'Y' END AS FK정보 , B.DEFAULT_VALUE AS 제약조건 -- , B.DEF_ORDER + 1 AS 컬럼순서 FROM DB_CLASS A INNER JOIN DB_ATTRIBUTE B ON B.CLASS_NAME = A.CLASS_NAME LEFT JOIN DB_INDEX C ON C.IS_PRIMARY_KEY = 'YES' AND C.CLASS_NAME = B.CLASS_NAME LEFT JOIN DB_INDEX_KEY D ON D.CLASS_NAME = C.CLASS_NAME AND D.INDEX_NAME = C.INDEX_NAME AND D.KEY_ATTR_NAME = B.ATTR_NAME LEFT JOIN DB_INDEX E ON E.IS_FOREIGN_KEY = 'YES' AND E.CLASS_NAME = B.CLASS_NAME LEFT JOIN DB_INDEX_KEY F ON F.CLASS_NAME = E.CLASS_NAME AND F.INDEX_NAME = E.INDEX_NAME AND F.KEY_ATTR_NAME = B.ATTR_NAME WHERE A.OWNER_NAME = 'TESTSCHEMA' AND (A.CLASS_NAME LIKE 'p_%' OR A.CLASS_NAME LIKE 't_%') ORDER BY A.CLASS_NAME, B.DEF_ORDER ; -- 인덱스정보 SELECT DISTINCT a.class_name AS [테이블명], a.index_name AS [인덱스명], b.key_attr_name AS [인덱스컬럼명], a.is_unique AS [UNIQE여부], a.is_primary_key AS [PK여부], a.is_foreign_key AS [FK여부], b.key_order AS [키순서] FROM db_index a, db_index_key b WHERE a.index_name = b.index_name AND a.class_name = b.class_name AND (a.class_name LIKE 's_%' OR a.class_name LIKE 's_%') ORDER BY a.index_name, b.key_order ; | cs |
#
728x90
반응형