MySQL Drop Tables.md

Various methods to drop all tables in a database without droping the database. NOTE you should test these on your own, they are copied from https://stackoverflow.com/questions/3476765/mysql-drop-all-tables-ignoring-foreign-keys

Single line from terminal

echo "DATABASE_NAME"| xargs -i{} sh -c "mysql -h HOST -u USER -p -Nse 'show tables' {}| xargs -i[] mysql -h HOST -u USER -p -e 'SET FOREIGN_KEY_CHECKS=0; drop table []' {}"

Using script

DB_HOST=xxx
DB_USERNAME=xxx
DB_PASSWORD=xxx
DB_NAME=xxx

CMD="mysql -sN -h ${DB_HOST} -u ${DB_USERNAME} -p${DB_PASSWORD} ${DB_NAME}"

# Generate the drop statements
TMPFILE=/tmp/drop-${RANDOM}.sql
echo 'SET FOREIGN_KEY_CHECKS = 0;' > ${TMPFILE}
${CMD} $@ >> ${TMPFILE} << ENDD
    SELECT concat('DROP TABLE IF EXISTS \`', table_name, '\`;')
    FROM information_schema.tables
    WHERE table_schema = '${DB_NAME}';
ENDD
echo 'SET FOREIGN_KEY_CHECKS = 1;' >> ${TMPFILE}

# Warn what we are about to do
echo
cat ${TMPFILE}
echo
echo "Press ENTER to proceed (or Ctrl-C to abort)."
read

# Run the SQL
echo "Dropping tables..."
${CMD} $@ < ${TMPFILE}
echo "Exit status is ${?}."
rm ${TMPFILE}```

## Use SQL to generate table list
`SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName';`