Mysql: queries to get tables by size, get table info

I’ve been using those for years, dropping here to avoid googling them each time

show tables along with size, order by size descending

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = <DB NAME>
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

show views

SELECT * FROM information_schema.views WHERE TABLE_SCHEMA=<DB NAME>

e.g. 
TABLE_CATALOG        | def
TABLE_SCHEMA         | tsidbuat
TABLE_NAME           | asn
VIEW_DEFINITION      | select ...
CHECK_OPTION         | NONE
IS_UPDATABLE         | YES
DEFINER              | db@%
SECURITY_TYPE        | DEFINER
CHARACTER_SET_CLIENT | utf8mb4
COLLATION_CONNECTION | utf8mb4_general_ci

show table details

SHOW columns FROM <TABLENAME>
e.g.
---------------+------------+------+-----+---------+-------+
| Field         | Type       | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| id            | char(36)   | NO   | MUL | <null>  |       |
| <field>       | int(30)    | YES  |     | <null>  |       |

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s