테이블 명세 조회 질의문

=========== 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 ;

오라클 열 합치기 (중복제거)

오라클 열 합치기 (중복제거)
REGEXP_REPLACE
LISTAGG
'([^,]+)(,\1)+', '\1'

REGEXP_REPLACE(
    LISTAGG(A.VEHC_NUMS,', ') WITHIN GROUP (ORDER BY A.VEHC_NUMS)
, '([^,]+)(,\1)+', '\1')

DATA-BASE 정보 (테이블,컬럼,인덱스)

-- ORACLE --
SELECT * FROM ALL_INDEXES;
SELECT * FROM ALL_IND_COLUMNS;
SELECT * FROM TABS;
SELECT * FROM ALL_ALL_TABLES;
SELECT * FROM ALL_TAB_COMMENTS;
SELECT * FROM COLS;
SELECT * FROM USER_COL_COMMENTS;
SELECT * FROM DICTIONARY WHERE TABLE_NAME IN ('TABS','ALL_ALL_TABLES','COLS','ALL_TAB_COMMENTS','USER_COL_COMMENTS','ALL_INDEXES','ALL_IND_COLUMNS');
/*
1 ALL_ALL_TABLES  Description of all object and relational tables accessible to the user
2 ALL_INDEXES     Descriptions of indexes on tables accessible to the user
3 ALL_IND_COLUMNS COLUMNs comprising INDEXes on accessible TABLES
4 ALL_TAB_COMMENTS  Comments on tables and views accessible to the user
5 USER_COL_COMMENTS Comments on columns of user's tables and views
6 COLS              Synonym for USER_TAB_COLUMNS
7 TABS              Synonym for USER_TABLES
*/
WITH AA AS (
SELECT TABLE_NAME
FROM TABS
WHERE SUBSTR(TABLE_NAME,1,4) = 'T_RM'
AND SUBSTR(TABLE_NAME,-4) NOT IN ('HIST','TEMP')
)
SELECT
    AA.TABLE_NAME, 'C' AS TYPE, TC.COMMENTS, C.COLUMN_NAME, C.DATA_TYPE, CC.COMMENTS
FROM AA
INNER JOIN ALL_TAB_COMMENTS TC ON AA.TABLE_NAME = TC.TABLE_NAME
INNER JOIN COLS C ON AA.TABLE_NAME = C.TABLE_NAME
INNER JOIN USER_COL_COMMENTS CC ON AA.TABLE_NAME = CC.TABLE_NAME AND C.COLUMN_NAME = CC.COLUMN_NAME
WHERE TC.OWNER = 'WCSUSER'
UNION ALL
SELECT AA.TABLE_NAME, 'I' AS TYPE, I.INDEX_NAME, LISTAGG(I.COLUMN_NAME,', ') WITHIN GROUP (ORDER BY I.COLUMN_NAME),'',''
FROM ALL_IND_COLUMNS I INNER JOIN AA ON AA.TABLE_NAME = I.TABLE_NAME
GROUP BY AA.TABLE_NAME, I.INDEX_NAME
ORDER BY 1,2
;
-- MS-SQL -- 
INFORMATION_SCHEMA.TABLES INFORMATION_SCHEMA.TABLE_CONSTRAINTS INFORMATION_SCHEMA.VIEW_TABLE_USAGE INFORMATION_SCHEMA.COLUMNS INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE INFORMATION_SCHEMA.KEY_COLUMN_USAGE INFORMATION_SCHEMA.VIEW_COLUMN_USAGE SYSOBJECTS
--테이블 코멘트 조회
SELECT OBJTYPE, OBJNAME, NAME, VALUE
FROM ::FN_LISTEXTENDEDPROPERTY (NULL, 'SCHEMA', 'DBO', 'TABLE', '테이블이름', DEFAULT, DEFAULT)
;
--컬럼 코멘트 조회
SELECT OBJTYPE, OBJNAME, NAME, VALUE FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'SCHEMA', 'DBO', 'TABLE', '테이블이름', 'COLUMN', DEFAULT)
;
SELECT * FROM INFORMATION_SCHEMA.TABLES;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS;
SELECT * FROM SYS.EXTENDED_PROPERTIES;
SELECT * FROM SYSUSERS; SELECT * FROM SYSOBJECTS;
SELECT * FROM SYSCOLUMNS;
SELECT /* OBJECT LIST */
    COL.TABLE_CATALOG, COL.TABLE_SCHEMA
, CASE OBJ.ID
WHEN LAG(OBJ.ID) OVER (ORDER BY COL.TABLE_CATALOG, COL.TABLE_SCHEMA, COL.TABLE_NAME, COL.ORDINAL_POSITION)
THEN NULL
ELSE OBJ.ID
END AS ID
, CASE COL.TABLE_NAME
WHEN LAG(COL.TABLE_NAME) OVER (ORDER BY COL.TABLE_CATALOG, COL.TABLE_SCHEMA, COL.TABLE_NAME, COL.ORDINAL_POSITION)
THEN ''
ELSE COL.TABLE_NAME
END AS TABLE_NAME
, CASE PROP_T.value
WHEN LAG(PROP_T.value) OVER (ORDER BY COL.TABLE_CATALOG, COL.TABLE_SCHEMA, COL.TABLE_NAME, COL.ORDINAL_POSITION)
THEN ''
ELSE PROP_T.value
END AS TABLE_COMMENT
,OBJ.ID,COL.TABLE_NAME,PROP_T.value AS TABLE_COMMENT
, COL.COLUMN_NAME
, PROP.value AS COLUMN_COMMENT
, COL.ORDINAL_POSITION
, CONVERT(VARCHAR,COL.DATA_TYPE) +'('+ ISNULL(CONVERT(VARCHAR,COL.CHARACTER_MAXIMUM_LENGTH),'') +')' AS DATA_TYPE
, COL.IS_NULLABLE
, COL.COLUMN_DEFAULT
FROM SYSOBJECTS OBJ
INNER JOIN INFORMATION_SCHEMA.COLUMNS COL ON OBJ.NAME = COL.TABLE_NAME
LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES PROP ON OBJ.ID = PROP.major_id
AND COL.ORDINAL_POSITION = PROP.minor_id
LEFT OUTER JOIN (
SELECT major_id,VALUE FROM SYS.EXTENDED_PROPERTIES WHERE minor_id=0
) PROP_T ON OBJ.ID = PROP_T.major_id
WHERE 0=0
ORDER BY COL.TABLE_CATALOG, COL.TABLE_SCHEMA, COL.TABLE_NAME, COL.ORDINAL_POSITION
;

SELECT /* COLUMN DETAILS */
CASE
OBJ.NAME WHEN LAG(OBJ.NAME) OVER(ORDER BY PROP.minor_id) THEN ''
ELSE OBJ.NAME
END AS TABLE_NAME
,OBJ.NAME
,ISNULL(COL.COLUMN_NAME,'[ '+OBJ.NAME+' ]') AS NAME
, COL.COLUMN_NAME
, PROP.VALUE AS COMMENT
, 'private String ' + LOWER(COL.COLUMN_NAME) + ';// ' + CONVERT(VARCHAR, PROP.VALUE) AS STR
, PROP.minor_id
FROM SYSOBJECTS OBJ
INNER JOIN SYS.EXTENDED_PROPERTIES PROP ON OBJ.ID = PROP.major_id
LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS COL ON OBJ.NAME = COL.TABLE_NAME
AND COL.ORDINAL_POSITION = PROP.minor_id
WHERE OBJ.TYPE = 'U'
AND PROP.NAME = 'MS_Description'
AND PROP.CLASS_DESC = 'OBJECT_OR_COLUMN'
ORDER BY OBJ.NAME ASC, PROP.minor_id
;