Report file sizes in proper units

Adding to my previous post about reporting time in proper units, I made a similar function to report file size in appropriate units. Again, the issue here is same as in my previous post (i.e. some backups are relatively small and some are really big).So,I was reporting all file sizes in bytes and it is kind of hard to understand the big numbers(It is easy understand 52428800 as 50.00 MB). So, to fix this, I made a scalar function which takes input in bytes and returns output in its nearest lower Bytes/KB/MB/GB/TB units.

Below is the function:

USE [msdb]
GO
CREATE FUNCTION [dbo].[fnBackupSize](@backupsize decimal(20,3))
RETURNS varchar(20)
AS
Begin
DECLARE @size varchar(20)
IF (@backupsize<1024)
Begin
Select @Size = cast(@backupsize as varchar(10)) +' Byte(s)'
End
IF (@backupsize>=1024 and @backupsize<=1048576)
Begin
Select @Size = cast(cast(@backupsize/1024 as decimal(20,3)) as varchar(10)) +' KByte(s)'
End
IF (@backupsize>=1048576 and @backupsize<=1073741824)
Begin
Select @Size = cast(cast(@backupsize/1048576 as decimal(20,3)) as varchar(10)) +' MByte(s)'
End
IF (@backupsize>=1073741824 and @backupsize<=1099511627776)
Begin
Select @Size =cast(cast(@backupsize/1073741824 as decimal(20,3)) as varchar(10)) +' GByte(s)'
End
IF (@backupsize>=1099511627776)
Begin
Select @Size =cast(cast(@backupsize/1099511627776 as decimal(20,3)) as varchar(10)) +' TByte(s)'
End

Return @size

End
Output: Select msdb.dbo.fnBackupSize(53687091200) as [Size]

Select msdb.dbo.fnBackupSize(53680) as [Size]

Advertisement