CREATE PROCEDURE GetDisks(@Drv char(1) = NULL)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @f tinyint = 0, @db sysname, @DefaultLogFolder sysname
	DECLARE @Sql nvarchar(MAX)
	
	DECLARE @Disks TABLE (Drv char(1), Total_MB int, Free_MB int, [Data] bit DEFAULT 0, Logs bit DEFAULT 0)
	DECLARE @Folders TABLE (Folder sysname)

	EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefaultLogFolder OUTPUT
	INSERT INTO @Disks (Drv, Free_MB) EXEC master..xp_fixeddrives
	IF NOT @Drv IS NULL
		DELETE FROM @Disks WHERE Drv <> @Drv

	UPDATE @Disks
		SET Total_MB = t.total_bytes / 1048576
		FROM @Disks d
			INNER JOIN(SELECT DISTINCT UPPER(LEFT(volume_mount_point,1)) AS Drv, total_bytes
						FROM sys.master_files f
							CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)) t ON d.Drv = t.Drv

	DECLARE cur CURSOR FOR SELECT Drv FROM @Disks
	OPEN cur
	FETCH NEXT FROM cur INTO @Drv
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @f = 0
		DELETE FROM @Folders

		INSERT INTO @Folders EXEC('EXEC master..xp_subdirs ''' + @Drv + ':\''')

		IF EXISTS(SELECT * FROM @Folders WHERE Folder='DATA')
			BEGIN
				SET @f = 1
				UPDATE @Disks SET [Data] = 1 WHERE Drv = @Drv
			END

		IF EXISTS(SELECT * FROM @Folders WHERE Folder='LOGS')
			BEGIN
				IF @f = 0 SET @f = 2
				UPDATE @Disks SET [Logs] = 1 WHERE Drv = @Drv
			END
		
		IF @f > 0 AND EXISTS(SELECT * FROM @Disks WHERE Drv = @Drv AND Total_MB IS NULL)
			BEGIN
				SET @db = 'z_disk_' + @Drv
				EXEC ('DROP DATABASE IF EXISTS ' + @db)

				SET @Sql ='
CREATE DATABASE ' + @db + '
	ON ( NAME = ' + @db + '_dat, FILENAME = ''' + @Drv + ':\' + CASE WHEN @f = 1 THEN 'DATA' ELSE 'LOGS' END + '\' + @db + '_dat.mdf'' )
	LOG ON ( NAME = ' + @db + '_log, FILENAME = ''' + @DefaultLogFolder + '\' + @db + '_log.ldf'' )'
				EXEC ( @Sql)

				UPDATE @Disks
					SET Total_MB = total_bytes / 1048576
					FROM @Disks d
						INNER JOIN  (SELECT UPPER(LEFT(volume_mount_point,1)) AS Drv, total_bytes
										FROM sys.database_files f
										CROSS APPLY sys.dm_os_volume_stats(DB_ID( @db ), f.file_id)) t ON d.Drv=t.Drv 
			END
		FETCH NEXT FROM cur INTO @Drv
	END
	CLOSE cur
	DEALLOCATE cur
	DELETE FROM @Disks WHERE [Data]=0 AND Logs=0 AND Drv <> 'C'
	SET NOCOUNT OFF

	SELECT * FROM @Disks
END

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetDataDisks(bool bAllDisks = true)
    {
        DriveInfo[] allDrives = System.IO.DriveInfo.GetDrives();

        SqlDataRecord record = new SqlDataRecord(
            new SqlMetaData("Drive", SqlDbType.NVarChar, 25),
            new SqlMetaData("VolumeLabel", SqlDbType.NVarChar, 50),
            new SqlMetaData("Size (MB)", SqlDbType.Float),
            new SqlMetaData("FreeSpace (MB)", SqlDbType.Float),
            new SqlMetaData("Percent Free Space", SqlDbType.Float),
            new SqlMetaData("Data", SqlDbType.Bit),
            new SqlMetaData("Logs", SqlDbType.Bit));

        SqlContext.Pipe.SendResultsStart(record);

        foreach (DriveInfo d in allDrives)
        {
            if (d.DriveType == DriveType.Fixed && d.IsReady == true)
            {
                bool bDataDisk = Directory.Exists(d.Name + "Data");
                bool bLogsDisk = Directory.Exists(d.Name + "Logs");

                if (bAllDisks == true || bDataDisk == true || bLogsDisk == true)
                {
                    record.SetValue(0, d.Name);
                    record.SetValue(1, d.VolumeLabel);
                    record.SetValue(2, (double)d.TotalSize / 1048576);
                    record.SetValue(3, (double)d.TotalFreeSpace / 1048576);
                    record.SetValue(4, (double)(d.TotalFreeSpace * 100) / d.TotalSize);
                    record.SetValue(5, bDataDisk);
                    record.SetValue(6, bLogsDisk);

                    SqlContext.Pipe.SendResultsRow(record);
                }
            }
        }

        SqlContext.Pipe.SendResultsEnd();
    }
}