Summarize Rails MySQL Database Tables
Copy and run summary.sql in MySQL console. Run
call showalltbl("tbl1,tbl2,tbl3"); to get a summary of
tbl3 ... in your MySQL database (haven't tried PostgresSQL etc).
Because of how ROR
manages/abstracts database, looking at the database tables, can give
quite some information about the application. For an app that I had
came across, I started with
select count(*) from <tbl1>; followed by
select * from <tbl1> limit 10; for the tables in the database, one
by one. But then I would look at a reference like
would try to recollect what
accounts table looked like. I would
rather have liked to get the above summary for all the tables, copy
them to a text editor and then rearrange and analyze them.
The script below defines procedure
showalltbl to get above mentioned
summary for all the tables, that are passed as comma separated list to
-- ref: http://stackoverflow.com/questions/7737970/procedure-to-loop-through-comma-separated-string-is-not-working-- ref: http://www.java2s.com/Code/SQL/Procedure-Function/EXECUTEdynamicSQLcommand.htmdelimiter $$drop procedure if exists execsql$$create procedure execsql(q varchar(1000))beginset @q = q;prepare stmt from @q;execute stmt;deallocate prepare stmt;end$$drop procedure if exists showtbl$$create procedure showtbl(tbl varchar(100))begincall execsql(concat("select \"", tbl, "\""));call execsql(concat("select count(*) as entries from ", tbl));call execsql(concat("select * from ", tbl, " limit 10"));end$$drop procedure if exists showalltbl$$create procedure showalltbl(strids varchar(1000))begindeclare strlen int default 0;declare substrlen int default 0;declare tbl varchar(100) default "";if strids is null thenset strids = '';end if;do_this:loopset strlen = char_length(strids);set tbl = substring_index(strids, ',', 1);call showtbl(tbl);set substrlen = char_length(tbl)+2;set strids = mid(strids, substrlen, strlen);if strids = null thenleave do_this;end if;if strids = '' thenleave do_this;end if;end loop do_this;end$$delimiter ;
How does this work?
There are three functions defined in the script. The most basic being
execsql. Let me start there. Its job is t take a string, and execute
it as an SQL statement.
Next up is
showtbl. It takes a table name as string. Uses
to select that string (so, the output shows the table name, to
understand which table rest of the summary belongs to). Selects count
on the table. And the show 10 entries from the table.
The last one is
showalltbl, which takes the comma separate string of
tables. Splits them and calls
showtbl on them.