SQLCLR source code

During my presentation, the last section “Use the CLR to manage the OS from SQL“, the slides gave a brief subset of code to implement the SQLCLR based new and improved xp_fixeddrives solution which provides a relational result set. This is something I actually wrote 4 years ago but even today works well and is a good example of why us DBA’s should like and use the CLR. Here, I provide the full source code which can be used to implement and extend your own solution.

First create your assembly. Compile the following code into a .NET DLL. If you would prefer a pre-compiled assembly to be emailed to you, then please get in touch or leave a message and I will happily do so.

using System;
using System.Collections.Generic;
using System.Text;
using sqltype = System.Data.SqlTypes;
using sql = Microsoft.SqlServer.Server;
using io = System.IO;

public class IO {
    //returns bytes
    [sql.SqlFunction()]
    public static sqltype.SqlInt64 DriveFree(sqltype.SqlString drive) {
        io.DriveInfo di;
        try {
            di = new System.IO.DriveInfo(drive.ToString());
            return di.TotalFreeSpace;
        }
        catch { return -1; }
    }

    [sql.SqlFunction()]
    public static sqltype.SqlInt64 DriveSize(sqltype.SqlString drive) {
        io.DriveInfo di;
        try {
            di = new System.IO.DriveInfo(drive.ToString());
            return di.TotalSize;
        }
        catch { return -1; }
    }

    [sql.SqlFunction()]
    public static sqltype.SqlInt64 DriveUsed(sqltype.SqlString drive) {
        io.DriveInfo di;
        try {
            di = new System.IO.DriveInfo(drive.ToString());
            return (di.TotalSize - di.TotalFreeSpace);
        }
        catch { return -1; }
    }

    [sql.SqlFunction()]
    public static sqltype.SqlDecimal DriveFreePercent(sqltype.SqlString drive) {
        io.DriveInfo di;
        try {
            di = new System.IO.DriveInfo(drive.ToString());
            decimal d = System.Convert.ToDecimal(di.TotalFreeSpace) * 100 /System.Convert.ToDecimal(di.TotalSize);
            sqltype.SqlDecimal sd = new System.Data.SqlTypes.SqlDecimal(d);
            return sd;
        }
        catch { return -1; }
    }

    [sql.SqlFunction()]
    public static sqltype.SqlDecimal DriveUsedPercent(sqltype.SqlString drive) {
        io.DriveInfo di;
        try {
            di = new System.IO.DriveInfo(drive.ToString());
            decimal d = System.Convert.ToDecimal(di.TotalSize - di.TotalFreeSpace) * 100 / System.Convert.ToDecimal(di.TotalSize);
            sqltype.SqlDecimal sd = new System.Data.SqlTypes.SqlDecimal(d);
            return sd;
        }
        catch { return -1; }
    }
}

Import the assembly and create the User Defined Functions which map to each of the SQLCLR functions.

CREATE ASSEMBLY SQLCallAssembly
FROM 'H:\SQLCallAssembly.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

CREATE FUNCTION dbo.udf_drivefree(@drive NVARCHAR(1))
RETURNS BIGINT
EXTERNAL NAME SQLCallAssembly.IO.DriveFree;
GO

CREATE FUNCTION dbo.udf_driveused(@drive NVARCHAR(1))
RETURNS BIGINT
EXTERNAL NAME SQLCallAssembly.IO.DriveUsed;
GO

CREATE FUNCTION dbo.udf_drivesize(@drive NVARCHAR(1))
RETURNS BIGINT
EXTERNAL NAME SQLCallAssembly.IO.DriveSize;
GO

CREATE FUNCTION dbo.udf_drivefreepercent(@drive NVARCHAR(1))
RETURNS DECIMAL(5,2)
EXTERNAL NAME SQLCallAssembly.IO.DriveFreePercent;
GO

CREATE FUNCTION dbo.udf_driveusedpercent(@drive NVARCHAR(1))
RETURNS DECIMAL(5,2)
EXTERNAL NAME SQLCallAssembly.IO.DriveUsedPercent;

Then put it all together by creating a VIEW, and write a single SELECT statement per drive letter tying each together by UNION statements. Bear in mind that this part of the solution can be refactored to reduce lines, but has been presented as such for simplicities sake.

CREATE VIEW vw_drivespace AS
SELECT 1 as 'seq', 'system' as drive,'C' as 'letter',
[dbo].[udf_drivefreepercent]('c') AS [%free],
[dbo].[udf_driveusedpercent]('c')as [%used],
[dbo].[udf_drivesize]('c')/1048576 AS [capacity(MB)],
[dbo].[udf_drivefree]('c')/1048576 AS [free(MB)],
[dbo].[udf_driveused]('c')/1048576 as [used(MB)]
UNION
SELECT 2 as 'seq', 'tempdb' as drive,'D' as 'letter',
[dbo].[udf_drivefreepercent]('d') AS [%free],
[dbo].[udf_driveusedpercent]('d')as [%used],
[dbo].[udf_drivesize]('d')/1048576 AS [capacity(MB)],
[dbo].[udf_drivefree]('d')/1048576 AS [free(MB)],
[dbo].[udf_driveused]('d')/1048576 as [used(MB)]
UNION
SELECT 3 as 'seq', 'log' as drive,'F' as 'letter',
[dbo].[udf_drivefreepercent]('f') AS [%free],
[dbo].[udf_driveusedpercent]('f')as [%used],
[dbo].[udf_drivesize]('f')/1048576 AS [capacity(MB)],
[dbo].[udf_drivefree]('f')/1048576 AS [free(MB)],
[dbo].[udf_driveused]('f')/1048576 as [used(MB)]
UNION
SELECT 4 as 'seq', 'data' as drive,'G' as 'letter',
[dbo].[udf_drivefreepercent]('g') AS [%free],
[dbo].[udf_driveusedpercent]('g')as [%used],
[dbo].[udf_drivesize]('g')/1048576 AS [capacity(MB)],
[dbo].[udf_drivefree]('g')/1048576 AS [free(MB)],
[dbo].[udf_driveused]('g')/1048576 as [used(MB)]
UNION
SELECT 5 as 'seq', 'backup' as drive,'H' as 'letter',
[dbo].[udf_drivefreepercent]('h') AS [%free],
[dbo].[udf_driveusedpercent]('h')as [%used],
[dbo].[udf_drivesize]('h')/1048576 AS [capacity(MB)],
[dbo].[udf_drivefree]('h')/1048576 AS [free(MB)],
[dbo].[udf_driveused]('h')/1048576 as [used(MB)]
GO