Came across a lot of databases that hold size of objects, files, whatever in bytes which is not the easiest to glance at and easily understand. So I wrote a basic SQL function to convert size in bytes to human readable format. The goal being that the output is greater than or equal to 1.0, that is automatically gets loops until it is less than 1.0 then stops at the previous division allowing for an easy to understand yet automatic format. I also have it round to 2 decimal places.
--set database to be used --USE BEDB; --checks if function already exists if it does it drops and re-adds it otherwise it adds it IF OBJECT_ID (N'dbo.f_ConvertSize') IS NOT NULL BEGIN DROP FUNCTION Dbo.F_ConvertSize; PRINT 'Function f_ConvertSize Alread Exists, Deleting it and Adding it back now' END ELSE BEGIN PRINT 'Function f_ConvertSize Does Not Exist, Adding it now.' END GO --creates function that --parameter: size in bytes --returns: single value nvarchar of converted size to readable format CREATE FUNCTION Dbo.F_ConvertSize ( @rawSize FLOAT ) Returns NVARCHAR(12) BEGIN --creates processing variables --div = value to divide by --counter = number of times divided DECLARE @div INT,@counter INT --unit is used to capture the unit measure used in the output DECLARE @unit NVARCHAR(3) --the sizes table variable is used to store the different units DECLARE @sizes TABLE (Id INT,Unit NVARCHAR(3)) INSERT @sizes(Id,Unit) VALUES(1,' B'),(2,' KB'),(3,' MB'),(4,' GB'),(5,' TB') --sets div and counter SET @div = 1024 SET @counter = 1 --while loop used to perform the conversion with condition clause that requires the size to be greater or equal to 1 WHILE @rawSize >= 1 AND @counter <= 5 BEGIN --breaks if the size is less than 1 after division --else it sets the rawsize to the rawsize divided by the div then it sets the counter to +=1 IF @rawSize/@div < 1 BREAK ELSE SET @rawSize = @rawSize/@div SET @counter += 1 END --gets units used from the units table variable SELECT @unit = Unit FROM @sizes WHERE Id = @counter --converts the new size to varchar and rounds it to 2 decimal places then adds the units and returns the output RETURN CAST(ROUND(@rawSize,2) AS NVARCHAR(50))+@unit END