MySQL: Sed for Scripts using “Show” from Command Line

Hi all,
So, a pretty basics one today… But useful to have handy. How to script an output from  mysql -B -e?

Easy, by using SED. Ok, by replacements are always tricky considering the line braking and etc. So, here goes an example with show tables:

Original Output.

[root@greporasrv ~]# mysql sbtest -B -e 'show tables'
Tables_in_sbtest
sbtest1
sbtest10
sbtest2
sbtest3
sbtest4
sbtest5
sbtest6
sbtest7
sbtest8
sbtest9

Cool, now with all in one line:

[root@greporasrv ~]# mysql sbtest -B -e 'show tables'|sed ':a;N;$!ba;s/\n/ /g'
Tables_in_sbtest sbtest1 sbtest10 sbtest2 sbtest3 sbtest4 sbtest5 sbtest6 sbtest7 sbtest8 sbtest9

Great, so let’s put some useful code on it:

[root@greporasrv ~]# mysql sbtest -B -e 'show tables'|sed ':a;N;$!ba;s/\n/ engine=innodb; \n alter table /g'
Tables_in_sbtest engine=innodb;
alter table sbtest1 engine=innodb;
alter table sbtest10 engine=innodb;
alter table sbtest2 engine=innodb;
alter table sbtest3 engine=innodb;
alter table sbtest4 engine=innodb;
alter table sbtest5 engine=innodb;
alter table sbtest6 engine=innodb;
alter table sbtest7 engine=innodb;
alter table sbtest8 engine=innodb;
alter table sbtest9 engine=innodb;

Hope you can use for your needs.

Cheers!