November 1, 2016 at 6:49 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 6:54 am
subrahmanyam113 (11/1/2016)
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
;
Hello and welcome to SSC. Please note that this forum is dedicated to MS SQL Server, and that you may find it more helpful if you can find a similar forum dedicated to Redshift in which to post your question.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply