[MARIADB] 테이블 및 컬럼 정보 조회 쿼리

#테이블정보 조회

SELECT * 

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = SCHEMA();

 

#컬럼정보 전체 조회

SELECT * 

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME = '테이블명'

ORDER BY ORDINAL_POSITION;

 

#컬럼정보 간단 조회

SELECT 

TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION AS NO, COLUMN_NAME, COLUMN_TYPE, 

COLUMN_DEFAULT AS `DEFAULT`, IS_NULLABLE,  COLUMN_KEY AS `KEY`, COLUMN_COMMENT

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = SCHEMA() 

AND TABLE_NAME = '테이블명'

ORDER BY ORDINAL_POSITION;

 

#현재 사용중인 DB의 전체 테이블 레이아웃

SELECT 

  A.ORDINAL_POSITION AS NO, A.TABLE_NAME, B.TABLE_COMMENT, A.COLUMN_NAME, A.COLUMN_TYPE

, IFNULL(A.COLUMN_DEFAULT, '') AS 'DEFAULT', A.COLUMN_KEY

, IF(A.IS_NULLABLE = 'YES','', A.IS_NULLABLE) AS NOT_NULL, A.COLUMN_COMMENT

FROM INFORMATION_SCHEMA.COLUMNS A, INFORMATION_SCHEMA.TABLES B

WHERE A.TABLE_SCHEMA = SCHEMA() 

  AND A.TABLE_NAME IN (

                    SELECT TABLE_NAME

                    FROM INFORMATION_SCHEMA.TABLES

                    WHERE TABLE_SCHEMA = SCHEMA()

                    )

  AND A.TABLE_CATALOG = B.TABLE_CATALOG

  AND A.TABLE_SCHEMA = B.TABLE_SCHEMA

  AND A.TABLE_NAME = B.TABLE_NAME

ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION;