How to get a list of all databases/database tables and their size via a command-line interface on a Plesk server

Connect to a Plesk via SSH.

Run one of the following commands:

• to get a list of all databases sorted by size:

  plesk db "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.TABLES GROUP BY table_schema ORDER BY ROUND(SUM(data_length + index_length)) DESC;"

• to get a list of all database tables sorted by size:

  plesk db "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;"

• to get a list of all database tables sorted by a database name:

  plesk db "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 (table_schema) ASC;"

Connect to a Plesk server via RDP.

Start a command prompt as an Administrator.

Run one of the following commands:

• to get a list of all databases sorted by size:

  C:> "%plesk_dir%MySQLbinmysql.exe" -u admin -p -P 3306 -e "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.TABLES GROUP BY table_schema ORDER BY ROUND(SUM(data_length + index_length)) DESC;"

• to get a list of all database tables sorted by size:

  C:> "%plesk_dir%MySQLbinmysql.exe" -u admin -p -P 3306 -e "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;"

• to get a list of all database tables sorted by a database name:

  C:> "%plesk_dir%MySQLbinmysql.exe" -u admin -p -P 3306 -e "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 (table_schema) ASC;"

  Note: The password for MySQL admin user is required. Refer to the documentation Changing Database Administrator’s Credentials to change it if needed.