Space in GB:
SELECT DatabaseName, TableName
,CAST(SUM(CurrentPerm) /1024**3 as DECIMAL (6,0)) AS ActualSpaceGB
,CAST(MAX(CurrentPerm)*(HASHAMP()+1) /1024**3 as DECIMAL (6,0)) as EffectiveSpaceGB
,EffectiveSpaceGB-ActualSpaceGB AS WasteSpaceGB
,CAST(100*(((MAX(CurrentPerm)*(HASHAMP()+1) /1024**3)-(SUM(CurrentPerm) /1024**3))/ (MAX(CurrentPerm)*(HASHAMP()+1) /1024**3)) as DECIMAL (6,0)) AS "WasteSpace%"
,CAST((100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100)) as DECIMAL (6,0)) AS SkewFactor
FROM DBC.TableSize
WHERE DatabaseName='<Database Name>'
GROUP BY 1,2
ORDER BY 5 DESC;
Space in MB:
SELECT DatabaseName, TableName
,CAST(SUM(CurrentPerm) /1024**2 as DECIMAL (6,0)) AS ActualSpaceMB
,CAST(MAX(CurrentPerm)*(HASHAMP()+1) /1024**2 as DECIMAL (6,0)) as EffectiveSpaceMB
,EffectiveSpaceMB-ActualSpaceMB AS WasteSpaceMB
,CAST(100*(((MAX(CurrentPerm)*(HASHAMP()+1) /1024**2)-(SUM(CurrentPerm) /1024**2))/ (MAX(CurrentPerm)*(HASHAMP()+1) /1024**2)) as DECIMAL (6,0)) AS "WasteSpace%"
,CAST((100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100)) as DECIMAL (6,0)) AS SkewFactor
FROM DBC.TableSize
WHERE DatabaseName='Expt'
GROUP BY 1,2
ORDER BY 5 DESC;