#!/bin/bash
function usage {
echo "Usage: $0 [OPTIONS]
-h host --optional
-P port --optional
-u user
-p password
-d dbname
-t tablename
-o outputfile"
}
if [ $# -lt 1 ]
then
usage
else
while getopts "h:p:u:P:d:t:o:" opt;
do
case ${opt} in
h) host=${OPTARG}
;;
o) outfile=${OPTARG}
;;
P) port=${OPTARG}
;;
u) user=${OPTARG}
;;
p) passwd=${OPTARG}
;;
d) dbname=${OPTARG}
;;
t) tablename=${OPTARG}
;;
esac
done
fi
host=${host:-localhost}
port=${port:-3306}
MYSQL="mysql -h ${host} -P ${port} -u ${user} -p${passwd} -ANe"
QUERY="SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT FROM information_schema.columns WHERE table_schema = '${dbname}' AND table_name = '${tablename}'"
value=$(${MYSQL} "${QUERY}")
echo "${value}"|awk '{out=""; for (i=4; i<=NF;i++){out=out" "$i};print $1"\t"$2"\t"$3"\t"out}' >>"$outfile".csv
#echo "${value}"|awk '{out=""; for (i=4; i<=NF;i++){out=out" "$i};print $1","$2","$3","out}' >> test1.csv