RE: query counts of a database
-Original Message- From: Elim Qiu [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 10:56 AM To: mysql@lists.mysql.com Subject: query counts of a database I'm looking for a query that reports the count of each table in the database. the query should not assume the table list of the database. Thanks for any inputs Is SELECT COUNT(*) FROM * too open-ended? BTW... do you want the number of records per table, or number of tables per database?? Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query counts of a database
If you are using MySQL 5.0 or later, use the INFORMATION_SCHEMA database. It has an in-memory table of table names called (as you would expect) 'tables'. SELECT table_rows,table_name FROM information_schema.tables WHERE table_schema = 'whatever database you choose'; If you are using a current database then do this: SELECT table_rows,table_name FROM information_schema.tables WHERE table_schema = DATABASE(); Or SELECT table_rows,table_name FROM information_schema.tables WHERE table_schema = SCHEMA(); Give it a try !!! -Original Message- From: Elim Qiu [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 11:56 AM To: mysql@lists.mysql.com Subject: query counts of a database I'm looking for a query that reports the count of each table in the database. the query should not assume the table list of the database. Thanks for any inputs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query counts of a database
Sorry, my email didn't make the intention clear. I want the number of records per table in a database like: querytime | -xx-xx-xx:xx:xx tNm1 | yyy tNm2 | zzz .. where tNm1, tNm2 are table names in the database. - Original Message - From: Boyd, Todd M. [EMAIL PROTECTED] To: Elim Qiu [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, June 13, 2008 10:02 AM Subject: RE: query counts of a database -Original Message- From: Elim Qiu [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 10:56 AM To: mysql@lists.mysql.com Subject: query counts of a database I'm looking for a query that reports the count of each table in the database. the query should not assume the table list of the database. Thanks for any inputs Is SELECT COUNT(*) FROM * too open-ended? BTW... do you want the number of records per table, or number of tables per database?? Todd Boyd Web Programmer No virus found in this incoming message. Checked by AVG. Version: 8.0.100 / Virus Database: 270.3.0/1501 - Release Date: 2008-6-13 6:33 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query counts of a database
Thanks a lot Edwards! I'm using MySQL 5.1. Your query works great! - Original Message - From: Rolando Edwards [EMAIL PROTECTED] To: Elim Qiu [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, June 13, 2008 10:32 AM Subject: RE: query counts of a database If you are using MySQL 5.0 or later, use the INFORMATION_SCHEMA database. It has an in-memory table of table names called (as you would expect) 'tables'. SELECT table_rows,table_name FROM information_schema.tables WHERE table_schema = 'whatever database you choose'; If you are using a current database then do this: SELECT table_rows,table_name FROM information_schema.tables WHERE table_schema = DATABASE(); Or SELECT table_rows,table_name FROM information_schema.tables WHERE table_schema = SCHEMA(); Give it a try !!! -Original Message- From: Elim Qiu [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 11:56 AM To: mysql@lists.mysql.com Subject: query counts of a database I'm looking for a query that reports the count of each table in the database. the query should not assume the table list of the database. Thanks for any inputs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
