担当しているサービスのテーブル定義書がなかったので自動生成するスクリプト作った。
動けばいい使い捨てコードなので設定ファイルとかないです。
table_definition.php
<!doctype html>
<html lang="ja">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<link href="https://fonts.googleapis.com/earlyaccess/notosansjapanese.css" rel="stylesheet">
<link href="https://fonts.googleapis.com/css?family=Lato:400,900" rel="stylesheet">
<title>table definition</title>
<style>
body {
background: #ccc;
color: #333;
margin: 0;
padding: 1% 3% 3%;
font-family: 'Lato', 'Noto Sans Japanese', '游ゴシック Medium', '游ゴシック体', 'Yu Gothic Medium', YuGothic, 'ヒラギノ角ゴ ProN', 'Hiragino Kaku Gothic ProN', 'メイリオ', Meiryo, 'ＭＳ Ｐゴシック', 'MS PGothic', sans-serif;
background: linear-gradient(to left, #8e9eab, #eef2f3);
background-repeat: no-repeat;
}
table {
border-collapse: collapse;
border: 1px solid #666;
width: 100%;
table-layout: fixed;
}
th, td {
border-left: 1px solid #666;
border-right: 1px solid #666;
font-size: 8.5pt;
}
td {
padding: 6px 6px;
color: #000;
word-wrap: break-word;
}
th {
background: rgba(120, 130, 140, 0.5);
color: #fff;
padding: 8px;
font-weight: normal;
font-size: 9pt;
}
th:nth-child(1) {
width: 14em;
}
th:nth-child(2) {
width: 7em;
}
th:nth-child(3) {
width: 8em;
}
th:nth-child(4) {
width: 3em;
}
th:nth-child(5) {
width: 3em;
}
th:nth-child(6) {
width: 4em;
}
th:nth-child(7) {
width: 8em;
}
tr {
background: #fff;
}
tr:nth-child(odd) {
background: #eee;
}
h2 {
font-size: 16pt;
font-weight: normal;
margin: 2em 0 0.5em;
}
h1 {
color: rgba(255, 255, 255, 0.4);
font-weight: bold;
font-size: 36pt;
text-align: right;
margin: 0;
}
</style>
</head>
<body>
<h1>abc</h1>
<table>
<?php
// 接続情報の設定
$pdo = new PDO('mysql:host=abc.def.local;dbname=abc;charset=utf8', 'abcuser', 'abcpswd123');
// テーブル一覧を取得
$sql = $pdo->prepare('show tables from abc');
$sql->execute();
// テーブル一覧
$tables = [];
foreach ($sql->fetchAll() as $row) {
// テーブル名
$tableName = $row['Tables_in_abc'];
// テーブルコメントを取得
$sql2 = $pdo->prepare("show table status like '$tableName'");
$sql2->execute();
$tableComment = '';
foreach ($sql2->fetchAll() as $row2) {
$tableComment = $row2['Comment'];
}
// カラム情報
$sql3 = $pdo->prepare("show full columns from $tableName");
$sql3->execute();
$columns = [];
foreach ($sql3->fetchAll() as $row3) {
$columns[] = array(
'field' => $row3['Field'],
'type' => $row3['Type'],
'collation' => $row3['Collation'],
'null' => $row3['Null'],
'key' => $row3['Key'],
'default' => $row3['Default'],
'extra' => $row3['Extra'],
'privileges' => $row3['Privileges'],
'comment' => $row3['Comment'],
);
}
$tables[$tableName] = array(
'name' => $tableName,
'comment' => $tableComment,
'columns' => $columns,
);
}
// html出力
foreach ($tables as $table) {
echo '<h2>'.$table['name'].' ('.$table['comment'].')</h2>';
echo '<table>';
// echo '<tr><th>Field</th><th>Type</th><th>Collation</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th><th>Privileges</th><th>Comment</th></tr>';
echo '<tr><th>Field</th><th>Type</th><th>Collation</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th><th>Comment</th></tr>';
foreach ($table['columns'] as $cols) {
echo '<tr>';
echo '<td>'.$cols['field'].'</td>';
echo '<td>'.$cols['type'].'</td>';
echo '<td>'.$cols['collation'].'</td>';
echo '<td>'.$cols['null'].'</td>';
echo '<td>'.$cols['key'].'</td>';
echo '<td>'.$cols['default'].'</td>';
echo '<td>'.$cols['extra'].'</td>';
// Privileges必要なら出してください
// echo '<td>'.$cols['privileges'].'</td>';
echo '<td>'.$cols['comment'].'</td>';
echo '</tr>';
}
echo '</table>';
}
?>
</table>
</body>
</html>