[MySQL] 쿼리로 테이블 정의서 추출하기

테이블 조회

SELECT
	t1.table_name,
	t1.table_comment
FROM
	(
	SELECT
		table_name,
		table_comment
	FROM
		information_schema.TABLES
	WHERE
		table_schema = '스키마명') t1
ORDER BY
	t1.table_name;

 

컬럼 조회

SELECT
	column_name,
	column_comment,
	column_type,
	column_key,
	CASE WHEN is_nullable = 'NO' THEN 'Y' ELSE '' END AS NOT_NULL,
	column_default,
	extra
FROM
	(
	SELECT
		table_name,
		table_comment
	FROM
		information_schema.TABLES
	WHERE
		table_schema = '스키마명') t1,
	(
	SELECT
		table_name,
		column_name,
		data_type,
		column_type,
		column_key,
		is_nullable,
		column_default,
		extra,
		column_comment,
		ordinal_position
	FROM
		information_schema.COLUMNS
	WHERE
		table_schema = '스키마명') t2
WHERE
	t1.table_name = t2.table_name
	AND t1.table_name = '테이블명' /* 이 라인 삭제시 모든 테이블 조회 */
ORDER BY
	t1.table_name,
	ordinal_position;