[mysql] 테이블 스키마 정보 조회, mysql table schema info

# 테이블 정보 조회 ( table info )
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = SCHEMA();


# 컬럼 정보 조회 ( column info )
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = SCHEMA()
AND TABLE_NAME = '{테이블명(table name)}'
ORDER BY ORDINAL_POSITION;


# 컬럼 간단 정보 조회 ( column simple info )
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 = '{테이블명(table name)}'
ORDER BY ORDINAL_POSITION;


# 현재 Databases에 있는 모든 Table 정보 조회 ( All tables in databases info )
SELECT
ORDINAL_POSITION AS NO, TABLE_NAME, COLUMN_COMMENT,  COLUMN_NAME, COLUMN_TYPE,  IF(IS_NULLABLE = 'YES','','V') AS NOT_NULL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = SCHEMA()
AND TABLE_NAME IN (
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = SCHEMA()
)
ORDER BY TABLE_NAME, ORDINAL_POSITION;


# 사용중인 것! ( I am using )
SELECT
ORDINAL_POSITION AS NO, COLUMN_COMMENT,  COLUMN_NAME, COLUMN_TYPE,  IF(IS_NULLABLE = 'YES','','V') AS NOT_NULL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = SCHEMA()
AND TABLE_NAME = 'Recommend' /* <-- (table name) */
ORDER BY ORDINAL_POSITION;