Drop multiple tables based on table name?

Dev Shed Forums Sponsor:

July 8th, 2007, 01:13 AM

y3468

Registered User

Join Date: May 2006

Posts: 21

Time spent in forums: 4 h 12 m 13 sec
Reputation Power: 0

Drop multiple tables based on table name?

I have about 30 or 40 tables all starting with a name prefix. Is there a way to drop all of these tables easily (mysql 4)?

July 8th, 2007, 02:55 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 7th Plane (8000 - 8499 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 8,257

Time spent in forums: 3 Months 2 Days 12 h 49 m 42 sec
Reputation Power: 524

This should make you think, the right way of grouping tables is putting them in a specific database, not using a prefix.

__________________
My blog
Tutorials about OSS databases, DBMonster ...
Contribute to OSS Development, fill bug reports!
Developer Shed eSupport
Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins
Random data (with a bias)

July 8th, 2007, 12:47 PM

y3468

Registered User

Join Date: May 2006

Posts: 21

Time spent in forums: 4 h 12 m 13 sec
Reputation Power: 0

I understand this, but up till about a year ago, godaddy.com only allowed one database (as apposed to now allowing 10 dbs). So to install multiple apps, a prefix is required.

So now, I'm stuck with a ton of tables with prefixes and am attempting to rebuild the server.

Is there a way to delete multiple tables with the same prefix?

July 9th, 2007, 11:24 PM

y3468

Registered User

Join Date: May 2006

Posts: 21

Time spent in forums: 4 h 12 m 13 sec
Reputation Power: 0

Ended up modding a script i found into two different scripts...
drop_tables.php - drops all tables with specified prefix
drop_tables_reverse.php - drops all tables that do not have specified prefix (very dangerous if not used correctly!)

Only db server, username, password, and database need to be changed in each script.

(I guess sometimes you just have to take a problem into your own hand - but seperate dbs for each web app is the best option if available)

---------------------------------------------------
DROP_TABLES.PHP
---------------------------------------------------

PHP Code:


		
			
<?php

// This script will make DROP queries for getting rid of tables that HAVE THE SAME PREFIX SPECIFIED

// (i.e. PHP-Nuke's tables nuke_*). This makes it easy to clean up your database after

// you remove such programs.

//

// Syntax http://www.yourserver.com/drop_tables.php?prefix="table_prefix"

//

// Created by Paul Ellis with code from www.php.net. http://www.ellisfoundation.com/freemusic/

// Modified by Brian Simecek - Handled some exceptions, and actually performed drop tables with status messages.





$dbhost = 'dbserver.com';

$dbuser = 'dbuser';

$dbpass = 'dbpass';

$dbname = 'dbname';





if (!mysql_connect($dbhost, $dbuser, $dbpass)) {

print 'Could not connect to mysql';

exit;

}



$result = mysql_list_tables($dbname);



if (!$result) {

   print "DB Error, could not list tables\n";

   print 'MySQL Error: ' . mysql_error();

   exit;

}



if ($_GET['prefix'] == null) {

   print "<b>TABLE LIST ($dbname):</b><br>";



   while ($row = mysql_fetch_row($result)) {

   print "$row[0]<br>";

   }

      print "<br><b>Syntax to drop multiple tables by prefix is 'drop_tables.php?prefix=prefix_that_the_tables_have</b>";

      } else {





while ($row = mysql_fetch_row($result)) {

   if (substr($row[0], 0, strlen($_GET['prefix'])) == $_GET['prefix']) {



      $tablesucess = "1";      



      $tablename = $row[0];

      $query = "DROP TABLE " . $tablename;



      $status = mysql_query("$query");

      print "DROP TABLE $row[0]";



      if ($status)

      {

      $status = "Success";

      }



      if (!$status)

      {

      die(mysql_error());

      $status = "Failure";

      }



      print "<b>   ...   " . $status . "</b><br>";





}

}

}





if (!$tablesucess)

{

      if ($row[0] == "")

      {

      print "<b>Prefix Does Not Match Any Tables Within Table $dbname.</b><br>";

      die();

      }

}



mysql_free_result($result);

?>

----------------------------------------------
DROP_TABLES_REVERSE.PHP
----------------------------------------------

PHP Code:


		
			
<?php

// This script will make DROP queries for getting rid of tables that DO NOT HAVE THE SPECIFIED PREFIX (I.E. DROP TABLES REVERSE)

// (i.e. PHP-Nuke's tables nuke_*). This makes it easy to clean up your database after

// you remove such programs.

//

// Syntax http://www.yourserver.com/drop_tables_reverse.php?prefix=table_prefix

//

// Created by Paul Ellis with code from www.php.net. http://www.ellisfoundation.com/freemusic/

// Modified by Brian Simecek - Handled some exceptions, and actually performed drop tables with status messages.

// This script removes any table that does not have the specified prefix.





$dbhost = 'dbhost.com';

$dbuser = 'dbuser';

$dbpass = 'dbpass';

$dbname = 'dbname';





if (!mysql_connect($dbhost, $dbuser, $dbpass)) {

print 'Could not connect to mysql';

exit;

}



$result = mysql_list_tables($dbname);



if (!$result) {

   print "DB Error, could not list tables\n";

   print 'MySQL Error: ' . mysql_error();

   exit;

}



if ($_GET['prefix'] == null) {

   print "<b>TABLE LIST ($dbname):</b><br>";



   while ($row = mysql_fetch_row($result)) {

   print "$row[0]<br>";

   }

      print "<br><b>Syntax to drop multiple tables by prefix is 'drop_tables.php?prefix=prefix_that_the_tables_have</b>";

      } else {





while ($row = mysql_fetch_row($result)) {

   if (substr($row[0], 0, strlen($_GET['prefix'])) <> $_GET['prefix']) {



      $tablesucess = "1";      



      $tablename = $row[0];

      $query = "DROP TABLE " . $tablename;



      $status = mysql_query("$query");

      print "DROP TABLE $row[0]";



      if ($status)

      {

      $status = "Success";

      }



      if (!$status)

      {

      die(mysql_error());

      $status = "Failure";

      }



      print "<b>   ...   " . $status . "</b><br>";





}

}

}





if (!$tablesucess)

{

      if ($row[0] == "")

      {

      print "<b>Prefix Does Not Match Any Tables Within Table $dbname.</b><br>";

      die();

      }

}



mysql_free_result($result);

?>

July 9th, 2007, 11:59 PM

helloakash

Contributing User

Join Date: Jan 2006

Location: India

Posts: 851

Folding Points: 15001 Folding Title: Novice Folder

Time spent in forums: 2 Weeks 2 Days 5 h 38 m 36 sec
Reputation Power: 543

Send a message via Google Talk to helloakash

You don't need to write a script if you have to do it only once. This thread may help you.

__________________
Akash Dwivedi
"Whatever the mind can conceive and believe, the mind can achieve."
Feel good..

July 10th, 2007, 08:16 AM

y3468

Registered User

Join Date: May 2006

Posts: 21

Time spent in forums: 4 h 12 m 13 sec
Reputation Power: 0

I'm not sure what you are telling me here...I have the same situation as the guy in the other post. The guy ended up using exactly the kind of script I made. I too have access to a cut down version of phpmysql and do not have direct access to the server.

Trust me, for this situation, it is so much easier to use the script and not have to worry about being an SQL master. I understand the SQL commands that they put together, but unless you have access to an SQL command line or will create a different PHP script to do the command for you...

July 10th, 2007, 08:40 AM

Guelphdad

Hockey face

Dev Shed God 6th Plane (7500 - 7999 posts)

Join Date: Nov 2001

Location: Guelph Canada

Posts: 7,543

Time spent in forums: 2 Months 2 Weeks 3 Days 6 h 6 m 38 sec
Reputation Power: 1040

you should modify your scripts to use mysql_real_escape_string() without it you are vulnerable to sql injection attacks that could drop all the tables in your database.

__________________
MySQL - reformat varchar dates to proper date type || MySQL - room vacancies || output results with column names with mysql only
Latest row per group || Deleting duplicates || Gaps in auto increment column
My site: sql help articles

July 10th, 2007, 10:32 AM

y3468

Registered User

Join Date: May 2006

Posts: 21

Time spent in forums: 4 h 12 m 13 sec
Reputation Power: 0

This script is not a permanent script for a site. It should only be used temporarily. Even with the function you specified, just executing the reverse script with any arbitrary prefix would remove every table in a database!

So what would be the sense in making the script more secure if the basic functionality would allow the same thing?

Viewing: Dev Shed Forums > Databases > MySQL Help > Drop multiple tables based on table name?

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

More Free IT White Papers!