Drop tables of a database from command line (windows or linux). MySQL.

Drop tables of a database from command line (windows or linux). MySQL.
How to drop tables from command line with a mysql database.





--setting a session (not global since we probably don't have enough privileges) with an higher length. Change the number/length as needed.
SET SESSION group_concat_max_len = 90000;

--setting a variable with the group concatenated list of the tables (ex.: table1,tables2;). Change the MYTABLENAMETOCHANGE with your own table
SET @mytables = (SELECT GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema = 'MYTABLENAMETOCHANGE');

--setting a variable so that we can have a full DROP (if table exists) statement
SET @drptables = (SELECT CONCAT('DROP TABLE IF EXISTS ', @mytables, ';'));

-- preparing "droppingtables" the sql statement from the @drptables variable
PREPARE droppingtables FROM @drptables;

-- executing the sql statement "droppingtables"
EXECUTE droppingtables;

-- releasing "droppingtables"
DEALLOCATE PREPARE droppingtables;



Sample Windows Batch file with command line including password.
Use -p instead of --password=yourpassword if you want to input it by hand.
Remember to add the error checkings or you will end up with the non execution of the queries

drop_tables.bat
________________________________________________________
________________________________________________________


@echo off
SET MYSQLPATH=C:\mysql\bin\
SET MYUSR=cart
SET MYPASS=cart
SET MYDB=cart
SET MYMAXLEN=90000

%MYSQLPATH%\mysql -u%MYUSR% --password=%MYPASS% -D %MYDB% -e "SET SESSION group_concat_max_len = %MYMAXLEN%;SET @mytables = (SELECT GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema = '%MYDB%');SET @drptables = (SELECT CONCAT('DROP TABLE IF EXISTS ', @mytables, ';'));PREPARE droppingtables FROM @drptables;EXECUTE droppingtables;DEALLOCATE PREPARE droppingtables;"
________________________________________________________
________________________________________________________



 Sample Bash script for unix
 drop_tables.sh
________________________________________________________
________________________________________________________
#!/bin/sh
SET MYUSR="cart"
SET MYPASS="cart"
SET MYDB="cart"
SET MYMAXLEN="90000"
MYSQLEXE="$(which mysql)"


#checking if mysql client exists
if [ -z "$MYSQL" ]; then
echo "Error: MYSQL not found"
exit 1
fi


$MYSQLEXE -u$MYUSR --password=$MYPASS -D $MYDB -e "SET SESSION group_concat_max_len = $MYMAXLEN;SET @mytables = (SELECT GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema = '$MYDB');SET @drptables = (SELECT CONCAT('DROP TABLE IF EXISTS ', @mytables, ';'));PREPARE droppingtables FROM @drptables;EXECUTE droppingtables;DEALLOCATE PREPARE droppingtables;"
________________________________________________________
________________________________________________________