MySQLでスキーマ情報を取得する

汎用な処理が必要になったので、あるだろうと思い調べてみたらありました。

MySQL 5.5で確認しています。

テーブル名を取得する

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'データベーススキーマ名' ORDER BY TABLE_NAME;

Viewも取れてしまうので、あとは工夫で絞ると良いと思います。

カラム情報を取得する

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'データベーススキーマ名' AND TABLE_NAME = 'テーブル名'
ORDER BY ORDINAL_POSITION;

汎用的にする

Function化して返すようにしています。

この処理は、CSVのヘッダ情報をカンマ区切り、ダブルクォーテーション付きで返すものです

---------------------------------------------------

CREATE FUNCTION GetColumnCommentsOfCSV(schemaName VARCHAR(255) , tableName VARCHAR(255)) RETURNS VARCHAR(2000)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE vColName VARCHAR(255);
  DECLARE vColComment VARCHAR(255);
  DECLARE vOutChar VARCHAR(2000);
  DECLARE curRec CURSOR FOR
    SELECT
        COLUMN_NAME
        ,COLUMN_COMMENT
    FROM
        INFORMATION_SCHEMA.COLUMNS
    WHERE 
        UPPER(TABLE_SCHEMA)   = UPPER(schemaName)   collate utf8_general_ci
      AND UPPER(TABLE_NAME)    = UPPER(tableName)    collate utf8_general_ci
    ORDER BY
        TABLE_NAME,ORDINAL_POSITION
    ;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  SET vOutChar = '';
  OPEN curRec;
  REPEAT
    FETCH curRec INTO vColName , vColComment;
    IF NOT done THEN
      IF LENGTH(vOutchar) > 0 THEN
        SET vOutchar = CONCAT(vOutChar,',');
      END IF;
      SET vOutchar = CONCAT(vOutChar,'"');
      SET vOutChar = CONCAT(vOutChar,vColComment);
      SET vOutchar = CONCAT(vOutChar,'"');
    END IF;
  UNTIL done END REPEAT;
  CLOSE curRec;
  RETURN vOutChar;
END;
//
DELIMITER ;

Oracleとか、SQLserverでもやってた記憶があるので、今度載せます。