How to query databases space of whole system.

We can query dbc.Diskspace to know all about space in system. here is query through which we can get idea about Maximum permanent space allocated, how much space is currently consumed and how much is free, with details about % of use and % of space usage skew.


SELECT Databasename AS "Database Name",
Max_perm_gb (DECIMAL (10,1)) AS "Max Perm (GB)",
current_perm_gb (DECIMAL (10,1)) AS "Current Perm (GB)",
EffectiveUsedSpace_Gb (DECIMAL (10,1)) AS "Effective Used Space (GB)",
Current_Perm_Percent AS "Current Perm %",
(Max_perm_gb (DECIMAL (10,1))) -( current_perm_gb (DECIMAL (10,1))) as "unused space in GB" ,
(100) - (Current_Perm_Percent)  as "unused %",
Peak_Perm_Gb (DECIMAL (10,1)) AS "Peak Perm (GB)",
Peak_Perm_Percent AS "Peak Perm %",
D_SKEW AS "Skew %"
FROM
(
SELECT
databasename, SUM(maxperm)/1024/1024/1024 AS Max_Perm_Gb,
SUM(currentperm)/1024/1024/1024 AS Current_Perm_Gb,
MAX(currentperm)*COUNT(*) /1024/1024/1024 AS EffectiveUsedSpace_Gb,
Current_Perm_Gb/Max_Perm_Gb * 100 AS Current_Perm_Percent,
SUM(peakperm)/1024/1024/1024 AS Peak_Perm_Gb,
Peak_Perm_Gb/Max_Perm_Gb * 100 AS Peak_Perm_Percent,
ZEROIFNULL((MAX(currentperm) - AVG(currentperm))/MAX(NULLIFZERO(currentperm)) * 100) AS D_SKEW
FROM dbc.diskspace
GROUP BY 1
) a
ORDER BY 2 DESC;