DBMS별 테이블 컬럼 정의서 추출 - 오라클, MYSQL, MSSQL, CUBRID, SYBASE, TIBERO

󰊱 [오라클] SQL 예시

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행

테이블 정의서 추출 SQL

SELECT S1.OWNER --OWNER명 , S1.TABLE_NAME --영문테이블명 , S2.COMMENTS --한글테이블명 , S1.NUM_ROWS --테이블볼륨 , S3.CREATED --등록일자 , S3.LAST_DDL_TIME --수정일자 FROM ALL_TABLES S1 , ALL_TAB_COMMENTS S2 , ALL_OBJECTS S3 WHERE 1=1 AND S1.OWNER = S2.OWNER AND S1.TABLE_NAME = S2.TABLE_NAME AND S1.OWNER = S3.OWNER AND S1.TABLE_NAME = S3.OBJECT_NAME AND S2.TABLE_TYPE = 'TABLE' AND S1.TABLE_NAME NOT LIKE '%TEMP%' AND S1.OWNER NOT IN ('SYS') AND S1.TABLESPACE_NAME NOT IN ('SYSTEM' , 'SYSAUX') AND S1.OWNER = 'OWNER명' -- Owner명 입력 ORDER BY S1.OWNER, S1.TABLE_NAME

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행

컬럼 정의서 추출 SQL

--오라클의 DB명은 직접 입력해야함 SELECT A.OWNER AS OWNER명, A.TABLE_NAME AS 테이블명_1, A.COLUMN_NAME AS 컬럼영문명_2, A.COL_CMT AS 한글컬럼명_3, A.NULLABLE AS NULL여부_6, A.DATA_TYPE AS 데이터타입_7, A.LEN 데이터길이_8, B.POS AS PK정보_9, C.FK_POS FK정보_10, A.DATA_DEFAULT AS 제약조건_11, A.COLUMN_ID AS 컬럼순서 FROM (SELECT S1.OWNER , S1.TABLE_NAME, S3.COMMENTS AS TAB_CMT, S1.COLUMN_NAME, S2.COMMENTS AS COL_CMT, S1.DATA_TYPE, CASE WHEN S1.DATA_PRECISION IS NOT NULL THEN DATA_PRECISION||','||DATA_SCALE ELSE TO_CHAR(S1.DATA_LENGTH) END AS LEN, NULLABLE, COLUMN_ID, DATA_DEFAULT FROM ALL_TAB_COLUMNS S1, ALL_COL_COMMENTS S2, ALL_TAB_COMMENTS S3 WHERE S1.OWNER = S2.OWNER AND S1.TABLE_NAME = S2.TABLE_NAME AND S1.COLUMN_NAME = S2.COLUMN_NAME AND S1.OWNER = S3.OWNER AND S1.TABLE_NAME = S3.TABLE_NAME ) A, (SELECT T1.OWNER, T1.TABLE_NAME, T2.COLUMN_NAME, 'PK'||POSITION AS POS FROM (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'P' )T1, (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, POSITION FROM USER_CONS_COLUMNS ) T2 WHERE T1.OWNER = T2.OWNER AND T1.TABLE_NAME = T2.TABLE_NAME AND T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME ) B, (SELECT T1.OWNER, T1.TABLE_NAME, T2.COLUMN_NAME, 'FK'||POSITION AS FK_POS FROM (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R' )T1, (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, POSITION FROM USER_CONS_COLUMNS ) T2 WHERE T1.OWNER = T2.OWNER AND T1.TABLE_NAME = T2.TABLE_NAME AND T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME ) C WHERE A.OWNER = B.OWNER(+) AND A.TABLE_NAME = B.TABLE_NAME(+) AND A.COLUMN_NAME = B.COLUMN_NAME(+) AND A.OWNER = C.OWNER(+) AND A.TABLE_NAME = C.TABLE_NAME(+) AND A.COLUMN_NAME = C.COLUMN_NAME(+) AND A.OWNER = 'OWNER명' ORDER BY A.TABLE_NAME, A.COLUMN_ID

 

 

 

 

󰊲 [MYSQL] SQL 예시

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행

테이블 정의서 추출 SQL

SELECT table_schema 'OWNER명' , TABLE_NAME '영문테이블명', , TABLE_COMMENT '한글테이블명' , TABLE_ROWS '테이블볼륨' , CREATE_TIME '등록일자' , UPDATE_TIME '수정일자' FROM information_schema.TABLES WHERE table_schema='OWNER명' --OWNER명 입력 AND TABLE_TYPE = 'BASE TABLE'

 

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행

컬럼 정의서 추출 SQL

SELECT t1.table_schema 'OWNER명', t1.table_name '테이블명_1', column_name '영문컬럼명_2', column_comment '한글컬럼명_3', CASE WHEN is_nullable = 'YES' THEN 'Y' WHEN is_nullable = 'NO' THEN 'N' END AS 'Null여부_6', COLUMN_TYPE '데이터타입_7', column_key 'PK정보_9', column_default '제약조건_11', extra 'Extra', ORDINAL_POSITION FROM (SELECT table_schema, table_name, table_comment FROM information_schema.TABLES) t1, (SELECT table_schema, table_name, ORDINAL_POSITION, column_name, COLUMN_TYPE,column_key, extra, is_nullable, column_default, column_comment FROM information_schema.COLUMNS) t2 WHERE t1.table_schema = t2.table_schema AND t1.table_name = t2.table_name AND t1.TABLE_SCHEMA = 'OWNER명 입력' ORDER BY t1.table_name, ordinal_position

󰊳 [MSSQL] SQL 예시

테이블 정의서 추출 SQL

SELECT A.TABLE_CATALOG, --OWNER명 DB_NAME() AS DBNAME, --DB명 A.TABLE_NAME, --테이블명 C.VALUE AS TABLE_COMMENT, --테이블 COMMENT i.rows, --테이블의 건수 J.create_date, --테이블생성일시 J.modify_date --테이블변경일시 FROM INFORMATION_SCHEMA.tables A INNER JOIN sysobjects o ON o.name = a.TABLE_NAME INNER JOIN sysindexes i ON o.id = i.id INNER JOIN sys.objects j ON J.NAME = A.TABLE_NAME LEFT OUTER JOIN ( SELECT object_id(objname) AS TABLE_ID, VALUE FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'User','dbo','table',NULL, NULL, NULL) ) C ON object_id(A.TABLE_NAME) = C.TABLE_ID WHERE i.indid < 2 AND o.xtype = 'U' AND A.TABLE_NAME <> 'sysdiagrams' AND A.TABLE_NAME NOT LIKE '%temp%' AND A.TABLE_NAME NOT LIKE '%test%' AND A.TABLE_CATALOG = 'OWNER명' --OWNER명 입력 ORDER BY A.TABLE_CATALOG, DB_NAME(), A.TABLE_NAME

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행

 

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행

컬럼 정의서 추출 SQL

SELECTB.TableName AS [테이블명_1] , A.COLUMN_NAME AS [영문컬럼명_2] , ISNULL(B.ColumnDescription,'') AS [한글컬럼명_3] ,CASE A.IS_NULLABLE WHEN 'YES'THEN 'Y' ELSE 'N' ENDAS [NULL여부] , CASE WHEN IdxType = 'C' THEN 'Y' ELSE '' ENDAS PK정보_9 , CASE WHEN FKey is not null THEN 'Y' ELSE '' ENDAS FK정보_10 , A.DATA_TYPEAS [데이터타입_7] , CASE WHEN A.CHARACTER_MAXIMUM_LENGTH IS NULL THEN ISNULL(A.NUMERIC_PRECISION, '') ELSE A.CHARACTER_MAXIMUM_LENGTH END AS [데이터길이_8] , ISNULL(A.COLUMN_DEFAULT, '') AS [제약조건_11] , CASE WHEN refID is not null THEN object_Name(refID) ELSE '' ENDAS [참조테이블] , CASE WHEN refColID is not null THEN col_name(refID, refColID) ELSE '' ENDAS [참조컬럼] , ''AS [유형] , ''AS [Validation Rule] ,A.ORDINAL_POSITIONAS [NO] FROM INFORMATION_SCHEMA.COLUMNS AS A WITH (NOLOCK) INNER JOIN ( SELECTA.nameAS TableName ,B.nameAS ColumnName ,C.Value AS ColumnDescription , D.FKey ,D.refID , D.refColID ,E.IdxType FROMsys.tables AS A WITH (NOLOCK) INNER JOIN sys.columns AS B WITH (NOLOCK) ON A.object_id = B.object_id left outer JOIN sys.extended_properties AS C WITH (NOLOCK) ON A.object_id = C.major_id AND B.column_id = c.minor_id left outer join ( selectf.name AS FKey, f.parent_object_id, fc.parent_column_id, fc.referenced_object_id refID, fc.referenced_column_id refColID fromsys.foreign_keys f inner join sys.foreign_key_columns fc on f.object_id = fc.constraint_object_id ) D on A.object_id = D.parent_object_id and B.column_id = D.parent_column_id left outer join ( select (case i.type when 0 then 'H' when 1 then 'C' else 'N' end) IdxType, i.object_id, c.column_id fromsys.indexes i inner join sys.index_columns c on i.index_id = c.index_id and c.object_id = i.object_id group by i.type, i.object_id, c.column_id ) E on A.object_id = E.object_id and B.column_id = E.column_id ) AS B ON A.TABLE_NAME = B.TableName AND A.COLUMN_NAME = B.ColumnName WHERE A.TABLE_CATALOG = 'OWNER명' order by 테이블명_1, NO

 

󰊴 [CUBRID) SQL 예시

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행(버전 10.X 기준)

테이블 정의서 추출 SQL

-10,x 버전 --큐브리드는 시스템 카탈로그에서 테이블건수 및 생성시간 수정시간 조회가 안됨 SELECT A.OWNER_NAME AS OWNER명 , A.CLASS_NAME AS 영문테이블명_2 , A.COMMENT AS 한글테이블명_3 FROM DB_CLASS A WHERE A.OWNER_NAME = 'OWNER명' AND A.is_system_class = 'NO' ORDER BY A.OWNER_NAME ,A.CLASS_NAME --9.x 버전 --큐브리드는 시스템 카탈로그에서 테이블건수 및 생성시간 수정시간 조회가 안됨 SELECT A.OWNER_NAME AS OWNER명 , A.CLASS_NAME AS 테이블명_2 , G.DESCRIPTION AS 한글테이블명_3 --DBA권한 필요 FROM DB_CLASS A LEFT JOIN _CUB_SCHEMA_COMMENTS G ON A.CLASS_NAME = G.TABLE_NAME AND G.COLUMN_NAME = '*' WHERE A.OWNER_NAME = 'OWNER명' AND A.is_system_class = 'NO' ORDER BY A.OWNER_NAME ,A.CLASS_NAME -- 큐브리드에서는 전체건수를 제공하지 않는다 --결과를 다시 질의하면 테이블의 건수를 알수있다 select 'select ''' + class_name + ''', count(*) from ' + class_name from db_class where is_system_class='NO'

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행(버전 10.X 기준)

컬럼 정의서 추출 SQL

--10.x 버전 SELECT A.CLASS_NAME AS 테이블명_1 , B.ATTR_NAME AS 컬럼영문명_2 , B.COMMENT AS 한글컬럼명_3 , B.IS_NULLABLE AS NULL여부_6 , B.DATA_TYPE AS 데이터타입_7 , B.PREC AS 데이터길이_8 , CASE WHEN D.KEY_ATTR_NAME IS NOT NULL THEN 'Y' END AS PK정보_9 , CASE WHEN F.KEY_ATTR_NAME IS NOT NULL THEN 'Y' END AS FK정보_10 , B.DEFAULT_VALUE AS 제약조건_11 , 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 = 'OWNER명 입력' ORDER BY B.DEF_ORDER --9.x 버전 SELECT A.OWNER_NAME AS OWNER명 A.CLASS_NAME AS 테이블명_1 , B.ATTR_NAME AS 컬럼영문명_2 , G.DESCRIPTION AS 한글컬럼명_3 --DBA권한 필요 , B.IS_NULLABLE AS NULL여부_6 , B.DATA_TYPE AS 데이터타입_7 , B.PREC AS 데이터길이_8 , CASE WHEN D.KEY_ATTR_NAME IS NOT NULL THEN 'Y' END AS PK정보_9 , CASE WHEN F.KEY_ATTR_NAME IS NOT NULL THEN 'Y' END AS FK정보_10 , B.DEFAULT_VALUE AS 제약조건_11 , 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 LEFT JOIN _CUB_SCHEMA_COMMENTS G ON A.CLASS_NAME = G.TABLE_NAME AND B.ATTR_NAME = G.COLUMN_NAME AND G.COLUMN_NAME <> '*' WHERE A.OWNER_NAME = 'OWNER명 입력' AND A.is_system_class = 'NO' ORDER BY A.CLASS_NAME , B.DEF_ORDER + 1

󰊵 [SYBASE] SQL 예시

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행

테이블 정의서 추출 SQL

SELECT user_name(T.creator) as Owner명 , db_name() as DB명 , T.TABLE_NAME as 영문테이블명 , trim(T.remarks) as 한글테이블명 , count as row수 , b.crdate as 테이블등록일시 , b.expdate as 테이블수정일시 from systable as T JOIN sysobjects as B on T.object_id = B.id where T.table_type = 'BASE' and B.TYPE = 'U' AND B.UID > 10 and user_name(T.creator) = 'Owner명' --Owner입력 ORDER BY user_name(T.creator), db_name(), T.TABLE_NAME

 

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행

컬럼 정의서 추출 SQL

SELECT user_name(T.creator) AS OWNER명, T.Table_name AS '테이블명_1', C.column_name AS '영문컬럼명_2', trim(C.remarks) as '한글컬럼명_3', case when C.nulls = 'N' then 'Y' else 'N' end as 'Null여부_6', D.domain_name as '데이터타입_7', C.width as '데이터길이_8', C.Pkey as 'PK정보_9', C."DEFAULT" as '제약조건_11', row_number() over (PARTITION BY T.Table_name order by C.Column_id) as '컬럼순서' from systable as T join SYSCOLUMN as C on (T.table_id = C.table_id) join SYSDOMAIN as D on (C.domain_id = D.domain_id) join sysobjects as E on T.object_id = E.id where T.table_type = 'BASE' AND E.UID > 10 AND E.TYPE='U' and user_name(T.creator) = 'OWNER 명 입력' order by T.table_id, C.column_id ;

 

󰊶 [TIBERO] SQL 예시

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행

테이블 정의서 추출 SQL

SELECT S1.TABLESPACE_NAME AS OWNER명, -- OWNER명 S1.TABLE_NAME AS 영문테이블명, -- 테이블명(영문), COMMENTS AS 한글테이블명, -- 테이블명(한글), NUM_ROWS AS 테이블볼륨, -- Row수. S3.CREATED AS 생성일자, -- 생성일자 S3.LAST_DDL_TIME AS 수정일자 -- 수정일자 FROM USER_TABLES S1, USER_TAB_COMMENTS S2, USER_OBJECTS S3 WHERE S1.TABLE_NAME = S2.TABLE_NAME AND S2.TABLE_TYPE = 'TABLE' AND TABLESPACE_NAME IS NOT NULL AND TABLESPACE_NAME = 'OWNER명' --OWNER명 입력 AND S1.TABLE_NAME = S3.OBJECT_NAME AND S1.TABLE_NAME NOT LIKE '%temp%' AND S1.TABLE_NAME NOT LIKE '%test%' ORDER BY S1.TABLESPACE_NAME , S1.TABLE_NAME

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행

컬럼 정의서 추출 SQL

SELECT A.OWNER AS OWNER명, A.TABLE_NAME AS 테이블명_1, A.COLUMN_NAME AS 컬럼영문명_2, A.COL_CMT AS 한글컬럼명_3, A.NULLABLE AS NULL여부_6, A.DATA_TYPE AS 데이터타입_7, A.LEN 데이터길이_8, B.POS AS PK정보_9, C.FK_POS FK정보_10, A.DATA_DEFAULT AS 제약조건_11, A.COLUMN_ID AS 컬럼순서 FROM (SELECT S1.OWNER , S1.TABLE_NAME, S3.COMMENTS AS TAB_CMT, S1.COLUMN_NAME, S2.COMMENTS AS COL_CMT, S1.DATA_TYPE, CASE WHEN S1.DATA_PRECISION IS NOT NULL THEN DATA_PRECISION||','||DATA_SCALE ELSE TO_CHAR(S1.DATA_LENGTH) END AS LEN, NULLABLE, COLUMN_ID, DATA_DEFAULT FROM ALL_TAB_COLUMNS S1, ALL_COL_COMMENTS S2, ALL_TAB_COMMENTS S3 WHERE S1.OWNER = S2.OWNER AND S1.TABLE_NAME = S2.TABLE_NAME AND S1.COLUMN_NAME = S2.COLUMN_NAME AND S1.OWNER = S3.OWNER AND S1.TABLE_NAME = S3.TABLE_NAME ) A, (SELECT T1.OWNER, T1.TABLE_NAME, T2.COLUMN_NAME, 'PK'||POSITION AS POS FROM (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'P' )T1, (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, POSITION FROM USER_CONS_COLUMNS ) T2 WHERE T1.OWNER = T2.OWNER AND T1.TABLE_NAME = T2.TABLE_NAME AND T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME ) B, (SELECT T1.OWNER, T1.TABLE_NAME, T2.COLUMN_NAME, 'FK'||POSITION AS FK_POS FROM (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R' )T1, (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, POSITION FROM USER_CONS_COLUMNS ) T2 WHERE T1.OWNER = T2.OWNER AND T1.TABLE_NAME = T2.TABLE_NAME AND T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME ) C WHERE A.OWNER = B.OWNER(+) AND A.TABLE_NAME = B.TABLE_NAME(+) AND A.COLUMN_NAME = B.COLUMN_NAME(+) AND A.OWNER = C.OWNER(+) AND A.TABLE_NAME = C.TABLE_NAME(+) AND A.COLUMN_NAME = C.COLUMN_NAME(+) AND A.OWNER = 'OWNER명 입력' ORDER BY A.TABLE_NAME, A.COLUMN_ID