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

오라클 열 합치기 (중복제거)
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
;