Last active
April 8, 2019 22:48
-
-
Save mrcnc/1d840d17d9e9650362e8e5dad641f529 to your computer and use it in GitHub Desktop.
show size of database tables
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- https://dev.mysql.com/doc/refman/8.0/en/tables-table.html | |
SELECT | |
table_name, | |
table_rows AS num_rows, | |
ROUND((data_length + index_length) / 1024 / 1024) AS size_mb | |
FROM | |
information_schema.TABLES | |
WHERE | |
TABLE_SCHEMA = 'your_schema_here' | |
ORDER BY | |
size_mb | |
DESC; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- adapted from https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminScripts/table_info.sql | |
SELECT TRIM(pgn.nspname) AS SCHEMA, | |
TRIM(a.name) AS TABLE, | |
CASE WHEN pgc.reldiststyle = 8 THEN a.rows_all_dist ELSE a.rows END AS num_rows, | |
b.mbytes AS size_mb | |
FROM (SELECT | |
db_id, | |
id, | |
name, | |
SUM(ROWS) AS ROWS, | |
MAX(ROWS) AS rows_all_dist | |
FROM pg_catalog.stv_tbl_perm a | |
GROUP BY db_id, id, name) AS a | |
JOIN pg_class AS pgc ON pgc.oid = a.id | |
JOIN pg_namespace AS pgn ON pgn.oid = pgc.relnamespace | |
LEFT OUTER JOIN (SELECT tbl, COUNT(*) AS mbytes FROM stv_blocklist GROUP BY tbl) b ON a.id = b.tbl | |
WHERE mbytes IS NOT NULL | |
AND pgn.nspname = 'your_schema_here' | |
AND pgc.relowner > 1 | |
ORDER BY size_mb DESC; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment