Jan 29, 2019 How to delete/drop all the tables from SQL Server Database without using Enterprise Manager? I tried using DROP Tables, Truncate Database, Delete and many more but it is not working.I want to delete all tables using Query Analyzer, i.e. Through SQL Query. Connect to the target database; Select all tables from the left panel; Right-click and choose delete, or simply hit delete button; Hit Cmd + S to commit changes to the server.
drop-mysql-tables.sh
#!/bin/bash |
# A shell script to delete / drop all tables from MySQL database. |
# Usage: ./script user password dbnane |
# Usage: ./script user password dbnane server-ip |
# Usage: ./script user password dbnane mysql.nixcraft.in |
# ------------------------------------------------------------------------- |
# Copyright (c) 2008 nixCraft project <http://www.cyberciti.biz/fb/> |
# This script is licensed under GNU GPL version 2.0 or above |
# ------------------------------------------------------------------------- |
# This script is part of nixCraft shell script collection (NSSC) |
# Visit http://bash.cyberciti.biz/ for more information. |
# ---------------------------------------------------------------------- |
# See URL for more info: |
# http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/ |
# --------------------------------------------------- |
MUSER='$1' |
MPASS='$2' |
MDB='$3' |
MHOST='localhost' |
[ '$4'!='' ] && MHOST='$4' |
# Detect paths |
MYSQL=$(which mysql) |
AWK=$(which awk) |
GREP=$(which grep) |
# help |
if [ !$#-ge 3 ] |
then |
echo'Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name} [host-name]' |
echo'Drops all tables from a MySQL' |
exit 1 |
fi |
# make sure we can connect to server |
$MYSQL -u $MUSER -p$MPASS -h $MHOST -e 'use $MDB'&>/dev/null |
if [ $?-ne 0 ] |
then |
echo'Error - Cannot connect to mysql server using given username, password or database does not exits!' |
exit 2 |
fi |
TABLES=$($MYSQL -u $MUSER -p$MPASS -h $MHOST$MDB -e 'show tables'|$AWK'{ print $1}'|$GREP -v '^Tables') |
# make sure tables exits |
if [ '$TABLES''' ] |
then |
echo'Error - No table found in $MDB database!' |
exit 3 |
fi |
# let us do it |
fortin$TABLES |
do |
echo'Deleting $t table from $MDB database...' |
$MYSQL -u $MUSER -p$MPASS -h $MHOST$MDB -e 'drop table $t' |
done |
Sign up for freeto join this conversation on GitHub. Already have an account? Sign in to comment
This question already has an answer here:
- MySQL DROP all tables, ignoring foreign keys 22 answers
How do I drop all tables in Windows MySQL, using command prompt? The reason I want to do this is that our user has access to the database drops, but no access to re-creating the database itself, for this reason we must drop the tables manually. Is there a way to drop all the tables at once? Bear in mind that most of the tables are linked with foreign keys so they would have to be dropped in a specific order.
MantasMantas
marked as duplicate by nawfal, fancyPants, Frédéric Hamidi, David, JaveFeb 12 '14 at 13:51
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
5 Answers
You can generate statement like this:
DROP TABLE t1, t2, t3, ...
and then use prepared statements to execute it:DevartDevart
The @Devart's version is correct, but here are some improvements to avoid having error. I've edited the @Devart's answer, but it was not accepted.
This script will not raise error with NULL result in case when you already deleted all tables in the database by adding at least one nonexistent - 'dummy' table.
And it fixed in case when you have many tables.
And This small change to drop all view exist in the Database
It assumes that you run the script from Database you want to delete. Or run this before:
Thank you to Steve Horvath to discover the issue with backticks.
KostanosKostanos
Try this.
This works even for tables with constraints (foreign key relationships). Alternatively you can just drop the database and recreate, but you may not have the necessary permissions to do that.
In order to overcome foreign key check effects, add
show table
at the end of the generated script and run many times until the show table
command results in an empty set.MutantMaheshMutantMahesh
You can drop the
database
and then recreate it with the below:-user1086159user1086159
The accepted answer does not work for databases that have large numbers of tables, e.g. Drupal databases. Instead, see the script here: https://stackoverflow.com/a/12917793/1507877which does work on MySQL 5.5. CAUTION: Around line 11, there is a 'WHERE table_schema = SCHEMA();' This should instead be 'WHERE table_schema = 'INSERT NAME OF DB INTO WHICH IMPORT WILL OCCUR';'
Community♦
Giles BGiles B