asked on

How to empty tables.

SugarCRM comes with an excellent script which sets up the whole system from blank - installing Apache, MySql, PHP, all required modules and options are configured, etc. Brilliant - just how software should be.

But then it assumes that you're an expert in the underlying stuff.

We chose the option to install the demo Sugar data and taht was very useful but now we've finished playing and want to use it for real, we need to get rid of the demo data. Is there an easy script to do this? No! The best advice we can get from the Sugar forums is to use the MySql admin tools to empty the tables in the database.

Fine.

How?

I can find a bin called "mysqladmin", I guess this can do it, but the info on syntax is not exactly beginner-friendly. I gues one needs to supply the database name (any clues what it would be) and password (this I can guess - Sugar Install kept asking me for passwords to use for different things and I know what I told it) and somehow to specify that I want ALL the (non-system) tables to be emptied...

SOLUTION

Umesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Umesh

If you have lots of tables and don't want to delete one by one.. then here is the easy solution for you....

Login to MySQL.. via command line or any GUI tools (you can download it from mysql.com freely).. and create below stored routine in one of schema...that's it...

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `truncatetables`$$
 
CREATE PROCEDURE `truncatetables`(in_schema varchar(100) )
    READS SQL DATA
BEGIN
  DECLARE l_table         varchar(100);
  DECLARE l_done          INT DEFAULT  0;
  DECLARE cursor1 cursor  FOR
    SELECT TABLE_NAME
	FROM   information_schema.TABLES
	WHERE  information_schema.TABLES.TABLE_SCHEMA=in_schema;
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
	OPEN cursor1;
	cursor_loop:LOOP
	    FETCH cursor1 INTO l_table;
	    IF l_done=1 THEN
		 LEAVE cursor_loop;
	    END IF;
    /*Do something with the row fetched*/
	  SET @tmp_sql= CONCAT("TRUNCATE ",in_schema,".",l_table);
	  PREPARE s1 FROM @tmp_sql;
	  EXECUTE s1;
	  DEALLOCATE PREPARE s1;
 	END LOOP cursor_loop;
	CLOSE cursor1;
	SET l_done=0;
END$$
 
DELIMITER ;
 
 
######### Test Case
When you call  procedure with a DATABASE name as parameter..it will truncate all the underlying tables in it.
 
 
call truncatetables('DBNAME_HERE');

Open in new window

ASKER CERTIFIED SOLUTION

Maciej S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Umesh

Please beware that above procedure would empty all the tables in the supplied schema... and if you want to selective tables then go by the comment one in which I have shown you how to truncate single tables..

Also, here is the link for admin tool from MySQL..

http://dev.mysql.com/downloads/gui-tools/5.0.html

SOLUTION

Cornelia Yoder

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ccomley

ASKER

Thanks - chose to do a re-install because there appear to be just SO many not-quite-standard elements to the way this is set up so I'd be for-ever trying to adjust your answers to fit the situation.