mysql_dump_scheme.sh

#!/bin/sh

# DB名や接続情報を元に、table名、カラム名の一覧CSVを生成する
# 例: mysql_dump_scheme.sh mydatabase > out.csv
# 例: mysql_dump_scheme.sh -u myuser -p 'xxx' mydatabase > out2.csv

dbname="$1"



export logfile="/tmp/run.log"
export outputfile="out.csv"

rm -f "$logfile" "$outputfile"

help() {
	echo "$(basename $0) dbname(and/or other mysql options)
print tablenames and column names of spedified dbname as CSV.
	" >&2
}

log() {
    echo "$*" >&2
}

sql() {
    echo "$*" | $sql_cmd
}

sql_cmd="mysql $dbname"

if [ "$dbname" = "" ];then
	help
	exit 1
fi
if sql "SELECT 1" > /dev/null;then
	:
else
	help
	exit 2
fi



#tables=$(echo "show tables" | $sql_cmd | sed -e '/^Tables_in_/d')
tables=$(sql "show tables"  | sed -e '/^Tables_in_/d' )

for t in $tables; do
    log "table $t"
    columns=$(sql "SHOW CREATE TABLE \`$t\`\\G" |  perl -ne 'if (/^\s*`([^`]+)`/) { print "$1\n"; }')
    for c in $columns; do
	    echo $t,$c
    done

done