Summarize Rails MySQL Database Tables

Copy and run summary.sql in MySQL console. Run call showalltbl("tbl1,tbl2,tbl3"); to get a summary of tbl1, tbl2, 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 a select * from <tbl1> limit 10; for the tables in the database, one by one. But then I would look at a reference like account_id and 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 itCould have achieved the same result in Ruby. But well..

-- 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.htm

delimiter $$

drop procedure if exists execsql$$
create procedure execsql(q varchar(1000))
begin
      set @q = q;
      prepare stmt from @q;
      execute stmt;
      deallocate prepare stmt;
end$$

drop procedure if exists showtbl$$
create procedure showtbl(tbl varchar(100))
begin
      call 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))
begin
  declare strlen    int default 0;
  declare substrlen int default 0;
  declare tbl varchar(100) default "";

  if strids is null then
    set strids = '';
  end if;

do_this:
  loop
    set 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 then
      leave do_this;
    end if;
    if strids = '' then
      leave 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 to take a string, and execute it as an SQL statement.

Next up is showtbl. It takes a table name as string. Uses execsql 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.