Created
January 24, 2017 07:08
-
-
Save enujo/8dd90857f4773c55e00e3b1d2f5a3f7d to your computer and use it in GitHub Desktop.
테이블 명세서를 위한 쿼리문
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- mysql 테이블 형식 추출 쿼리 | |
SELECT | |
t1.table_name, ORDINAL_POSITION, column_name 'Column Name', COLUMN_TYPE 'COLUMN TYPE', column_key 'Key', | |
case | |
when is_nullable = 'YES' THEN 'Y' | |
when is_nullable = 'NO' THEN 'N' | |
END AS 'Null able', | |
column_default 'Default Value', column_comment 'Comment',t1.table_comment,extra 'Extra' | |
FROM | |
(SELECT | |
table_name, table_comment | |
FROM | |
information_schema.TABLES WHERE table_schema='데이터베이스 이름') t1, | |
(SELECT | |
table_name,ORDINAL_POSITION, column_name, COLUMN_TYPE,column_key, extra, is_nullable, column_default, column_comment | |
FROM | |
information_schema.COLUMNS WHERE table_schema='데이터베이스 이름') t2 | |
WHERE | |
t1.table_name = t2.table_name | |
ORDER BY | |
t1.table_name, ordinal_position; | |
-- mysql 테이블 추출 쿼리 | |
SELECT | |
ORDINAL_POSITION '필드순번', | |
COLUMN_NAME '필드명', | |
DATA_TYPE '데이터 TYPE', | |
COLUMN_TYPE '데이터 LENGTH', | |
COLUMN_KEY 'KEY', | |
IS_NULLABLE 'NULL값여부', | |
EXTRA '자동여부', | |
COLUMN_DEFAULT '디폴트값', | |
COLUMN_COMMENT '필드설명' | |
FROM | |
`information_schema`.COLUMNS | |
WHERE | |
TABLE_SCHEMA = '데이터베이스 이름' | |
AND TABLE_NAME = '테이블 명' | |
ORDER BY | |
TABLE_NAME, ORDINAL_POSITION |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment