USE [YourDB]
GO

-- CREATE A CONVERSION LOOKUP TABLE
CREATE TABLE dbo._SDE_ByteConversion (
   UOM VARCHAR(16) PRIMARY KEY,
   Value DECIMAL(38,0) NOT NULL,
   Suffix VARCHAR(16) NOT NULL
);
GO

-- GRANT SELECT RIGHTS FOR SMS/ConfigMgr REPORTS
GRANT SELECT ON dbo._SDE_ByteConversion TO smsschm_users, webreport_approle;
GO

--POPULATE THE TABLE WITH UNITS OF MEASURE, VALUES AND SUFFIXES
INSERT INTO dbo._SDE_ByteConversion VALUES('Bytes',1,' Bytes');
INSERT INTO dbo._SDE_ByteConversion VALUES('Byte',1,' Bytes');
INSERT INTO dbo._SDE_ByteConversion VALUES('B',1,' Bytes');
INSERT INTO dbo._SDE_ByteConversion VALUES('Kilobytes',1024,' KB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Kilobyte',1024,' KB');
INSERT INTO dbo._SDE_ByteConversion VALUES('KB',1024,' KB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Megabytes',1048576,' MB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Megabyte',1048576,' MB');
INSERT INTO dbo._SDE_ByteConversion VALUES('MB',1048576,' MB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Gigabytes',1073741824,' GB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Gigabyte',1073741824,' GB');
INSERT INTO dbo._SDE_ByteConversion VALUES('GB',1073741824,' GB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Terabytes',1099511627776,' TB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Terabyte',1099511627776,' TB');
INSERT INTO dbo._SDE_ByteConversion VALUES('TB',1099511627776,' TB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Petabytes',1125899906842624,' PB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Petabyte',1125899906842624,' PB');
INSERT INTO dbo._SDE_ByteConversion VALUES('PB',1125899906842624,' PB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Exabytes',1152921504606846976,' EB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Exabyte',1152921504606846976,' EB');
INSERT INTO dbo._SDE_ByteConversion VALUES('EB',1152921504606846976,' EB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Zettabytes',1180591620717411303424,' ZB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Zettabyte',1180591620717411303424,' ZB');
INSERT INTO dbo._SDE_ByteConversion VALUES('ZB',1180591620717411303424,' ZB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Yottabytes',1208925819614629174706176,' YB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Yottabyte',1208925819614629174706176,' YB');
INSERT INTO dbo._SDE_ByteConversion VALUES('YB',1208925819614629174706176,' YB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Brontobytes',1237940039285380274899124224,' BB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Brontobyte',1237940039285380274899124224,' BB');
INSERT INTO dbo._SDE_ByteConversion VALUES('BB',1237940039285380274899124224,' BB');
INSERT INTO dbo._SDE_ByteConversion VALUES('Geopbytes',1267650600228229401496703205376,' GeopBytes');
INSERT INTO dbo._SDE_ByteConversion VALUES('Geopbyte',1267650600228229401496703205376,' GeopBytes');
GO


-- CREATE THE FUNCTION TO DO THE CONVERSION
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Number2 (John Nelson) - http://number2blog.com
-- Create date: 2009-07-02
-- Description: Convert bytes from one unit of measure (Bytes, KB, MB, GB, TB, PB, EB, ZB, YB, BB, Geopbytes)
--              To any other unit of measure (Bytes, KB, MB, GB, TB, PB, EB, ZB, YB, BB, Geopbytes)
--              in a human readable format.  (128 GB, 1000 Bytes, etc.)
-- INPUT: @InputNumber - Decimal(38,7)
--        @InputUOM - VARCHAR(11)
--        @OutputUOM - VARCHAR(11)
--
-- OUTPUT: VARCHAR(49)
--
-- DEPENDENCIES: Requires a table called dbo._SDE_ByteConversion which holds the UOM, the 
-- value and the human-readable UOM suffix (GB,TB, etc)
-- =============================================
CREATE FUNCTION [dbo].[udf_ConvertBytes]
(
   @InputNumber   DECIMAL(38,7),
   @InputUOM      VARCHAR(11) = 'Bytes',
   @OutputUOM     VARCHAR(11) = 'Gigabytes'
)
RETURNS VARCHAR(64)
WITH SCHEMABINDING
AS
BEGIN
   --convert the inputnumber to bytes (the subselect) and then divide the 
   --bytes by the value of the OutputUOM and add the suffix to do the conversion
      RETURN (
      SELECT 
         CAST((SELECT Value*@InputNumber FROM dbo._SDE_ByteConversion WHERE UOM = @InputUOM)/Value AS VARCHAR(49)) + Suffix
      FROM dbo._SDE_ByteConversion 
      WHERE UOM = @OutputUOM
)     

END;

-- GRANT RIGHTS TO SMS/ConfigMgr REPORTS TO EXECUTE THE FUNCTION
GRANT EXECUTE ON dbo.udf_ConvertBytes TO smsschm_users, webreport_approle;
GO


--AND TEST IT TO MAKE SURE IT WORKS (CONVERT THE DISK SIZE MB TO GB)
SELECT
   ResourceID,
   dbo.udf_ConvertBytes(Size0,'MB','GB') AS Size
FROM
   dbo.v_GS_Logical_Disk
WHERE
   Name0 = 'C:'