Skip navigation links

User Comments

Posted by arjan muyen on June 18 2004 10:57am[Delete] [Edit]

I needed to drop 38 tables on database php1 and could not find a metadata table on which I could execute SQL like: delete from metadata where table_name like 'phpbb208%'
Workaround-Solution:

mysqlshow -uroot -p php1 phpbb208\\_% |sed 's/[|+-]//g'|sed 's/[ ]*$/,/'>drop208.sql
(**note the \\ to get the phpbb208_% wildcard**)

provided me the table_name, table_name, ... list
editing drop208.sql and adding 'drop table' provided me the complete drop-statement.

mysql php1 -u root -p < drop208.sql

Done

Posted by Nan Li on April 7 2005 2:48am[Delete] [Edit]

Another example use of mysqlshow.
I wanted to have all the tables in a database that contains a certain field.
Here is the bash shell script I wrote:

#!/bin/sh
#This scripts returns all the tables in a database that contains some field

function usage
{
echo "Usage: $0 USER DB COLUMN"
}

function ExistsColumn
{
local USER=$1
local DB=$2
local TABLE=$3
local COLUMN=$4

SEARCH_RESULT=$(mysqlshow -u ${USER} ${DB} ${TABLE} ${COLUMN} | awk '{ if ( NR == 5) print $2 }')
if [ "${COLUMN}" = "${SEARCH_RESULT}" ];
then
echo "true";
else
echo "false";
fi
}

function main
{
local USER=$1
local DB=$2
local COLUMN=$3

if [[ "${USER}" = "" || "${DB}" = "" || "${COLUMN}" = "" ]];
then
usage
exit 1
fi

all_tables=$(mysqlshow -u ${USER} ${DB} | \
awk '{ if (NR >4 ) print $_}' | \
sed -e 's/[|+-]//g; /^$/d ' | \
xargs )
for TABLE in ${all_tables}; do
if [ "true" = "$(ExistsColumn $USER $DB $TABLE $COLUMN)" ];
then
echo $TABLE
fi
done

}

main $*

Posted by Arnoldas Brazys on November 16 2010 8:44pm[Delete] [Edit]

Was searching how to describe all tables in database, here goes a one liner

echo "show tables;" | mysql table_name | grep -v "Tables_in_" | while read table; do echo "Table: $table"; (echo "describe $table" | mysql -t table_name); done >> show_tables_verbose.txt

Posted by joshua sox on December 4 2013 8:23pm[Delete] [Edit]

This script using mysqlshow will dump every user database to a folder marked by the current date. you can also dump system databases by using -u root -p password in the command. the paths created and used are relative so create a dir where you want to keep them and place the script inside.

#! /bin/bash
d=`date +%y%m%d`
db=$(mysqlshow | sed '1,/Databases/d' | awk '{print $2}')
mkdir -p $d-dump
cd $d-dump
for dbs in $db; do mysqldump $dbs > $d-$dbs.sql
wait
done
cd ../
exit