Drive Letter Finder

CREATE PROCEDURE dbo.sp_FindAvailebleDriveLetter
AS
BEGIN
    --*******************************************************************************************************************************************************
    -- Description - A routine which uses DOS commands to identify the first available drive letter on the local machine
    -- Programmer - Darren Sunderland
    --*******************************************************************************************************************************************************

    /*
    Declare variables and temp tables
    */    CREATE TABLE #RET
    (
        RowID    int IDENTITY(1,1) NOT NULL,
        RowReturn    varchar(1000)
    )
    DECLARE @chDrvChk    char(1)
    DECLARE @chDrive    char(1)
    DECLARE @vcReturn     varchar(1000)
    DECLARE @vcCommand     varchar(1500)
    DECLARE @inLetter    int
    DECLARE @inCount    int

    /*
    Set command to return drive list and load into temporary table
    */    SET @vcCommand = 'echo list volume | diskpart'
    INSERT INTO #RET (RowReturn)EXEC @vcReturn=master..xp_cmdshell @vcCommand

    /*
    Strip the kludge from the table and format correctly
    */    DELETE FROM #RET WHERE RowReturn NOT LIKE '%Volume%' OR RowReturn IS NULL
    DELETE FROM #RET WHERE RowReturn LIKE '%Ltr%'
    UPDATE #RET SET RowReturn=LTRIM(RIGHT(RTRIM(LTRIM(REPLACE(RowReturn,'Volume ',''))),LEN(RTRIM(LTRIM(REPLACE(RowReturn,'Volume ',''))))-3))

    /*
    Find first available drive, working backwards from Z
    */    SET @inLetter=90
    WHILE @inLetter > 64 BEGIN
        SET @chDrvChk=NCHAR(@inLetter)
        SELECT @inCount=COUNT(*) FROM #RET WHERE LEFT(RowReturn,1)=@chDrvChk
        IF @inCount=0 BEGIN
            SET @chDrive = @chDrvChk
            SET @inLetter = 1
        END
    
        SET @inLetter = @inLetter - 1
    END
    
    /*
    If the count is at 64, no drives are available. Set default return value
    */    IF @inLetter = 64
        SET @chDrive = '*'
    
    DROP TABLE #RET
    
    SELECT @chDrive
END


GO