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