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;
|