1
mysql -u root -pmysql;

SELECT TABLE_NAME AS "Table Name", table_rows AS "Quant of Rows", ROUND((data_length + index_length)/1024/1024,2) AS "Total Size Mb"  FROM information_schema.TABLES WHERE information_schema.TABLES.table_schema='database_name';

I want to create a shell script which will execute these command and return the result.

How may i run this commands through shell script ?

krunal shah
  • 335
  • 1
  • 3
  • 13

3 Answers3

6

Yet another answer...

mysql -uroot -pmysql -B --skip-column-names -e'SELECT TABLE_NAME AS "Table Name", table_rows AS "Quant of Rows", ROUND((data_length + index_length)/1024/1024,2) AS "Total Size Mb"  FROM information_schema.TABLES WHERE information_schema.TABLES.table_schema="database_name"'

Also, if you are going to have the password in the script, you should secure it so it isn't readable by people you don't wanting to know the password.

user9517
  • 115,471
  • 20
  • 215
  • 297
TomKro
  • 136
  • 2
3
mysql -u root -pmysql << eof
SELECT TABLE_NAME AS "Table Name", table_rows AS "Quant of Rows", ROUND((data_length + index_length)/1024/1024,2) AS "Total Size Mb"  FROM information_schema.TABLES WHERE information_schema.TABLES.table_schema='database_name';
eof
krunal shah
  • 335
  • 1
  • 3
  • 13
3

Or, equivalently, something like this:

echo 'SELECT TABLE_NAME AS "Table Name", table_rows AS "Quant of Rows", ROUND((data_length + index_length)/1024/1024,2) AS "Total Size Mb" FROM information_schema.TABLES WHERE information_schema.TABLES.table_schema="database_name";' | mysql -u root -pmysql

EDIT: fixed quoting.

Phil Hollenback
  • 14,947
  • 4
  • 35
  • 52