Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 is

WIRCES  WIRCES_TEST

  450              530

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 .

450    530

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.

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.