2022년 5월 10일 화요일

테이블 명세 조회 질의문

=========== MYSQL ======================================================================== SELECT * FROM information_schema.TABLES ; SELECT * FROM information_schema.COLUMNS ; SELECT T.TABLE_NAME ,C.COLUMN_NAME ,C.ORDINAL_POSITION ,C.COLUMN_KEY , ifnull( IF(C.COLUMN_DEFAULT='NULL',NULL,C.COLUMN_DEFAULT),'') AS COLUMN_DEFAULT ,C.IS_NULLABLE ,C.COLUMN_TYPE ,T.TABLE_COMMENT ,C.COLUMN_COMMENT ,T.TABLE_COLLATION ,C.COLLATION_NAME FROM information_schema.TABLES T INNER JOIN information_schema.COLUMNS C ON T.TABLE_CATALOG = C.TABLE_CATALOG AND T.TABLE_SCHEMA = C.TABLE_SCHEMA AND T.TABLE_NAME = C.TABLE_NAME WHERE 0=0 AND T.TABLE_CATALOG ='' AND T.TABLE_SCHEMA = '' ORDER BY T.TABLE_NAME , C.ORDINAL_POSITION ; =========== ORACLE ======================================================================== WITH CONS AS( SELECT UC.CONSTRAINT_NAME ,UC.CON_TYPE ,UC.CONSTRAINT_TYPE ,UC.TABLE_NAME ,UC.GENERATED ,UC.STATUS ,UC.INDEX_NAME ,UCC.COLUMN_NAME ,UCC.POSITION FROM USER_CONSTRAINTS UC INNER JOIN USER_CONS_COLUMNS UCC ON UC.OWNER = UCC.OWNER AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME AND UC.TABLE_NAME = UCC.TABLE_NAME WHERE 0=0 AND UC.OWNER = '' AND UC.CONSTRAINT_TYPE ='P' --ORDER BY UC.TABLE_NAME ASC,UCC.POSITION ASC ) SELECT TC.TABLE_NAME ,C.COLUMN_NAME ,TC.COMMENTS AS T_COMMENTS ,CC.COMMENTS AS C_COMMENTS ,C.COLUMN_ID ,NVL2(CONS.CONSTRAINT_TYPE ,'PK',NULL) AS CONS ,C.DATA_TYPE ,C.DATA_LENGTH ,C.NULLABLE ,C.DATA_DEFAULT FROM ALL_TAB_COMMENTS TC INNER JOIN ALL_TAB_COLUMNS C ON TC.OWNER = C.OWNER AND TC.TABLE_NAME = C.TABLE_NAME INNER JOIN ALL_COL_COMMENTS CC ON TC.OWNER = CC.OWNER AND TC.TABLE_NAME = CC.TABLE_NAME AND C.COLUMN_NAME = CC.COLUMN_NAME LEFT OUTER JOIN CONS ON TC.TABLE_NAME = CONS.TABLE_NAME AND C.COLUMN_NAME = CONS.COLUMN_NAME WHERE 0=0 AND TC.OWNER = '' AND TC.TABLE_TYPE = 'TABLE' ORDER BY TC.TABLE_NAME ASC, C.COLUMN_ID ASC ; =========== CUBRID ======================================================================== SELECT * FROM DB_CLASS WHERE 0=0 AND OWNER_NAME='' ; SELECT * FROM DB_ATTRIBUTE WHERE 0=0 ; SELECT * FROM DB_INDEX; SELECT T.CLASS_NAME ,C.ATTR_NAME ,T.COMMENT AS T_COMMENT ,C.COMMENT AS C_COMMENT ,C.DEF_ORDER ,C.DATA_TYPE ,C.PREC ,C.SCALE ,C.IS_NULLABLE ,C.COLLATION FROM DB_CLASS AS T INNER JOIN DB_ATTRIBUTE AS C ON T.OWNER_NAME='' AND T.CLASS_TYPE = 'CLASS' AND T.CLASS_NAME = C.CLASS_NAME WHERE 0=0 ORDER BY T.CLASS_NAME,C.DEF_ORDER ;