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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply