How can I drop all the tables in a mysql database? 2

[adgteq]

hi,

How can I drop all the tables in a mysql database without droping the database?

Thank you.

 

[tshot]

u can simly use 'rm *' command to delete all tables of thta dir (database)




----------------
ur feedback is a very welcome desire

 

[tshot]

but if u wihh to satick to mysql only then

drop table tab1, tab2, ... ;

----------------
ur feedback is a very welcome desire

 

[adgteq]

hi, tshot

how can I find out where is the database dir?
I have not much permissions on the server and I have not created the database myself. Where usually the databases are?

Thanx

 

[adgteq]

and I can't drop the tables by name because they're many.
is there a way to drop them all by a mysql syntax? (for example using a wildcard character)

 

[sleipnir214]

I'd just drop and recreate the whole database.

If I did not have sufficient permissions for that, I'd issue a SHOW TABLES query and process the return into either a single DROP TABLE query or a set of DROP TABLES queries.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[tshot]

sleipnir214 is a better way. tHowever, 2 find out the databe i,e ur directory u can try

mysqladmin -h <yr server> -p <ur passwd> variables | grep data

as asfra ai know there is no wildcrad for dropping tables




----------------
ur feedback is a very welcome desire

 

[adgteq]

answer to: sleipnir214

Thank you. I think that's a good Idea. But I just know sql basics and I don't know how to do this.
Would you please give me the codes needed? or just tell me the syntaxes I must know.
Regarding that I just have a shell access to the server.

Thanx

 

[adgteq]

answer to: tshot

Thank you. I now know that my database is located in /var/lib/mysql/adgteq_d
but I can't get in there because the owner is "mysql".

By the way, Thank you

 

[dkdude]

adgteq,

If you use PHP, then you can use the function mysql_list_tables() to get a list of all tables in a given database. You can use it to make a function that automatically drops all tables in that database.

I wrote a small example (quite chunky but functional). I can post it if you like ...

Good Luck §:O)


Jakob

 

[dkdude]

adgteq,

Okay - here it goes (for all to see, rather than mailing it to you).

Sorry - it IS a bit messy, but it works:

<?

// GENERIC OPEN DATABASE FUNCTION --------------------------------------------

  function open_db() {
    $dblink = mysql_connect($GLOBALS[&quot;dbhost&quot;], $GLOBALS[&quot;dbuser&quot;], $GLOBALS[&quot;dbpassw&quot;]);
    mysql_select_db($GLOBALS[&quot;dbname&quot;], $dblink);
    return $dblink;
  }

// CREATE TEST TABLE FUNCTION ------------------------------------------------

  function newTable($dbName) {
    $dblink = open_db();
    echo &quot;Creating test table <B>$dbName</B><BR>&quot;;
    $res = mysql_query(&quot;CREATE TABLE $dbName (id INT NOT NULL AUTO_INCREMENT,
                        PRIMARY KEY(id))&quot;);
    if(!$res)
      echo &quot;Fejl : &quot;.mysql_error();
    else
      echo &quot;<B>$dbName</B> is created<BR><BR>&quot;;
    mysql_close($dblink);
  }

// CREATE A BUNCH OF TEST TABLES FUNCTION ------------------------------------

  function createTestTables() {
    newTable(&quot;test1&quot;);
    newTable(&quot;test2&quot;);
    newTable(&quot;test3&quot;);
  }

// DROP ALL TABLES FUNCTION --------------------------------------------------

  function dropAllTables($dbname) {
    // fetch table names into $tableNames array
    $result = mysql_list_tables($dbname);
    if (!$result) {
      echo &quot;DB Error, could not list tables\n&quot;;
      echo 'MySQL Error: ' . mysql_error();
      exit;
    }
    while ($row = mysql_fetch_row($result)) {
      $tableNames[] = $row[0];
    }
    mysql_free_result($result);
    // table names fetched

    // build SQL &quot;DROP&quot; string
    $drop = &quot;DROP TABLE &quot;;
    for($tableCount=0;$tableCount<count($tableNames);$tableCount++) {
      $drop .= $tableNames[$tableCount];
      if($tableCount<count($tableNames)-1)  // only add a comma if not last table!
        $drop .= &quot;,&quot;;
    }
    // DROP string, $drop, is build ...

    // ... now, drop all tables:
    $dblink = open_db();
    $result = mysql_query($drop)
      or die(&quot;Invalid query: &quot; . mysql_error());
    mysql_close($dblink);
    echo &quot;All tables in $dbname are dropped!&quot;;
  }

// MAIN ----------------------------------------------------------------------

  $dbhost = &quot;localhost&quot;;
  $dbname = &quot;test&quot;;
  $dbuser = &quot;******&quot;;
  $dbpassw = &quot;******&quot;;

  $mode = &quot;make&quot;;

  switch($mode) {
    case &quot;make&quot; : createTestTables();
    break;

    case &quot;drop&quot; : dropAllTables(&quot;test&quot;);
    break;
  }
?>

Just put in your MySQL user and password (and change the database name if you like).

Good Luck §;O)


Jakob

 

[dkdude]

... One more note : run it with $mode = "make" to create a few test tables, then run it with $mode = "drop" to call the drop function!

Regards


Jakob

 

[tshot]

Before i procced further i have a doubt. and i am addresing all who have partcipated in this reply. i
is it possible that is ts prodcution database and no permission to remove table ?
other wise it simply to ask the admin. besides, dropping all the tables is as good as removing the database and recreating. And it has no hassels at all.

so are we unwittingly doing some thing wrong ?

else the client used to connect whether PHP or graphicals like MySql Front or PhpMyAdmin allow for such kind of job ?

Please, i am not doubting adgteq, integrty. It just came to my mind for somehting that happened.







----------------
ur feedback is a very welcome desire

 

[dkdude]

Hi tshot,

I can't speak for adgteq. However, the (5) hosts (3 different suppliers) I use comes with one database only and the host won't allow me to delete that database, hence the need for a function to drop all tabels in that one database.

adgteq, I'm guessing that the same goes for you. Am I right?

On my local development machine I just drop the entire database with WinMySqlAdmin -absolutely the fastest solution, as pointed out by you, tshot §;O)

Best Regards


Jakob

 

[adgteq]

Excuse me if I'm late.

1. dkdude, your neat code solved my problem. Thanks

2. to tshot: (sorry, maybe I couldn't have explained my conditions) I don't have permission to create a database, but I can do anything in my database (such as to remove tables) when I login to mysql.

3. yes, dkdude is right. Although I didn't try to delete the database, because I don't want to ask the admin to create it again for me if it worked

Thanks to all.

 

[tshot]

well then i seem to miss out somewhere because what i know in my sql is when u get drop privielge is drop tables and datatabses. in fact thread436-693374 was about this only.

pls correct me if i am wrong. below what i have read in the latest manula online
The CREATE and DROP privileges allow you to create new databases and tables, or to drop (remove) existing databases and tables.

if there is way to safe gurad database pls post it here. i wish to do same at my place




----------------
ur feedback is a very welcome desire

 

[dkdude]

tshot,

I found this in the online manual:

http://www.mysql.com/doc/en/Adding_users.html

Haven't tried it, but looks like it's what you need to define MySQL privileges! (that's what you were looking for, right?)

Good Luck §;O)


Jakob

 

[tshot]

but where does it specify that u can have drop option only for tables ? what i had cited was also an excerpt from on line manual

http://www.mysql.com/doc/en/Privileges_provided.html

----------------
ur feedback is a very welcome desire

 

[dkdude]

Hi tshot,

I've been looking and looking without any luck...

Perhaps you should try to start a new thread on that topic -surely it must be possible?!

Regards


Jakob