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
add comment

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.