November 1, 2016 at 7:59 am
Hi I am using a script to get details about database name, schema name, table name, row count and size of each table in GB. I would like to add one extra column '% of Disk Utilization' to the table which will calculate the percentage of size utilized by each table. This should be like
% of Disk Utilization = [(Size of each table)/(Total size of all tables)]*100
Also, this column values should be sorted out in descending order.
Can any one help me out please.
Here is the script that needs to be edited.
SELECT db_name
,schema_name
,table_name
,row_count
,size_in_gb
FROM (SELECT id table_id
,datname db_name
,nspname schema_name
,relname table_name
,SUM(rows) row_count
FROM stv_tbl_perm
JOIN pg_class
ON pg_class.oid = stv_tbl_perm.id
JOIN pg_namespace
ON pg_namespace.oid = relnamespace
JOIN pg_database
ON pg_database.oid = stv_tbl_perm.db_id
GROUP BY id, datname, nspname, relname
ORDER BY id, datname, nspname, relname) tbl_det
JOIN (SELECT tbl table_id
,ROUND(CONVERT(REAL,COUNT(*))/1024,2) size_in_gb
FROM stv_blocklist bloc
GROUP BY tbl) tbl_size
ON tbl_size.table_id = tbl_det.table_id
;
November 1, 2016 at 8:07 am
Duplicate post, please respond only to the other thread Redshift Script.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy