Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am trying to write a script which allows a user to select the what manipulation he needs to do on a table. I want to check if the table exists or not. If it exists I will continue the other things or else I exit saying table doesn't exist. How might I achieve this.

share|improve this question

4 Answers 4

if [ $(mysql -N -s -u root -p -e \
    "select count(*) from information_schema.tables where \
        table_schema='db_name' and table_name='table_name';") -eq 1 ]; then
    do something
else
    echo "table <table_name> does not exist"
    exit 1
fi
  • -N to skip column names
  • -s for nontabular output
share|improve this answer
2  
To have your script log in to MySQL without stopping and prompting you for a password, you can create a .my.cnf file in your home directory and add a [client] block containing password="my_password". Have a look for the section on user option files in the manual. –  Ladadadada Nov 24 '11 at 7:49
    
thanks, good one –  Fedir Mar 14 '13 at 16:23

If you want a method that doesn't involve running a mysql client and logging in to the mysql server, you can do this:

if ls -l /var/lib/mysql/database/table.frm 2> /dev/null > /dev/null
then
  echo Table exists
else
  echo Table does not exist
fi

You will have to replace the path to your data directory, your database name and your table name in the first line.

Caveats:

  1. This will not work if you are using InnoDB and are not using innodb-file-per-table. i.e. The script can say the table does not exist even though it does.
  2. It's possible for that file to exist for various reasons including copying tables around using the filesystem or switching from MyISAM to InnoDB. i.e. The script can say that the table does exist even though it doesn't.

It's not as reliable as the other methods presented already because of the reasons above but it avoids the mysql client so it may still be worthwhile.

Note that all methods presented here rely on your getting your own permissions (filesystem or MySQL) correct. If you get them wrong you will most likely get a false negative.

share|improve this answer

In MySQL you can use -e from the shell

mysql -e "desc main_db.clients" > /dev/null 2>&1
echo $?
share|improve this answer

For this problem I call the following method as root:

if (( $(su postgres -c "psql -l | grep mytablename" | wc -l ) == 0  ));
then
    echo "table does not exist"; 
fi

Note that this is for postgresql for mysql you should change the psql -l to mysql's list table command (ie. the line in Glen's answer).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.