How to Display All The Tables with More Information (Create Date, Size,...) in a MySQL Database

How to display all the tables with more information (create date, size,...) in a MySQL database?

INFORMATION_SCHEMA.TABLES is the most easily queryable, but that doesn't have creation date.
show table status does return creation date information. You can probably craft something to get the table name out of information_schema, then call show table status on each.

How to get the sizes of the tables of a MySQL database?

You can use this query to show the size of a table (although you need to substitute the variables first):

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
    AND table_name = "$TABLE_NAME";

or this query to list the size of every table in every database, largest first:

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

How do I get the creation date of a MySQL table?

You would query the information_schema for the create_time of the table.

For instance:

SELECT create_time FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'your_schema'
  AND table_name = 'your_table'

Reference: http://dev.mysql.com/doc/refman/5.0/en/tables-table.html

How can I describe all tables in the database through one statement?

There is no statement that describe all tables at once. But you may want to do something like this :

SELECT * FROM information_schema.columns WHERE table_schema = 'db_name';

Get record counts for all tables in MySQL database

SELECT SUM(TABLE_ROWS) 
     FROM INFORMATION_SCHEMA.TABLES 
     WHERE TABLE_SCHEMA = '{your_db}';

Note from the docs though: For InnoDB tables, the row count is only a rough estimate used in SQL optimization. You'll need to use COUNT(*) for exact counts (which is more expensive).

In a mysql database replace all dates with a specific date

If I got you right this will set all the dates to 2021-12-31

UPDATE jos_content 
SET modified = '2021-12-31’

---

Related Topics