[DB] 테이블 스키마 컬럼 정보 조회 쿼리

1. MSSQL 테이블 스키마 주요 정보 쿼리

SELECT

A.TABLE_NAME AS 테이블명

, C.VALUE AS TABLE_COMMENT

, A.COLUMN_NAME AS 컬럼명

, A.IS_NULLABLE AS 널여부

, A.DATA_TYPE AS 데이터타입

, A.CHARACTER_MAXIMUM_LENGTH AS 길이

, A.NUMERIC_PRECISION AS 숫자전체길이

, A.NUMERIC_SCALE AS 소수점이하길이

, A.COLUMN_DEFAULT

, B.VALUE AS COLUM_COMMENT

FROM INFORMATION_SCHEMA.COLUMNS A

LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES B

ON B.major_id = object_id(A.TABLE_NAME)

AND A.ORDINAL_POSITION = B.minor_id

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 A.TABLE_NAME IN ('테이블명1', '테이블명2', '테이블명3')

ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION;

​

​

2. ORACLE 테이블 스키마 주요 정보 쿼리

SELECT

A.OWNER

, A.TABLE_NAME AS 테이블명

, '' AS 테이블설명

, A.COLUMN_NAME AS 컬럼명

, A.NULLABLE AS 널여부

, A.DATA_TYPE AS 데이터타입

, A.DATA_LENGTH AS 데이터길이

, A.DATA_PRECISION AS 숫자전체길이

, A.DATA_SCALE AS 소수점이하길이

, A.DATA_DEFAULT AS 기본값

, B.COMMENTS

FROM ALL_TAB_COLUMNS A, ALL_COL_COMMENTS B

WHERE A.OWNER = B.OWNER

AND A.TABLE_NAME = B.TABLE_NAME

AND A.COLUMN_NAME = B.COLUMN_NAME

AND A.OWNER = '소유자'

AND A.TABLE_NAME IN ('테이블명1', '테이블명2', '테이블명3')

ORDER BY A.TABLE_NAME, A.COLUMN_ID

​

3. MARIA DB

SELECT

TABLE_SCHEMA

, TABLE_NAME

. ''

, COLUMN_NAME

, IS_NULLABLE

-- , COLUMN_KEY

, COLUMN_TYPE

, COLUMN_DEFAULT

, COLUMN_COMMENT

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = '스키마명'

and TABLE_NAME LIKE '%%'

ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

;