I am trying to get the total number of tables present in each database and export it to excel sheet. so i had tried this
mysql -h192.168.0.xxx -P -uxxxx -pxxxx information_schema -e " select max(case when TABLE_SCHEMA='WIRCES' then (select count(*) from TABLES where TABLE_SCHEMA='WIRCES') ELSE NULL END) 'WIRCES', max(case when TABLE_SCHEMA='WIRCES_TEST' then (select count(*) from TABLES where TABLE_SCHEMA='WIRCES_TEST')ELSE NULL END) 'WIRCES_TEST' from TABLES into outfile '/SMART2/SPACE.xls' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';"
when the above is executed without 'into outfile ....' Result of query in mysql isWIRCES WIRCES_TEST
when export to excel i get only the count of tables without any column name in the excel sheet.
Inside excel sheet it is displaying only the count like below without any column name .
I am trying to add this in a .sh script. I need database name as column name and total count of tables as row.. I had also tried " column_name as 'alias' " ,"column_name alias". But still i don't get what the problem is. Kindly help me .
Thanks in advance.