Determining if a Folder Exists Using xp_cmdshell

The code below creates a stored proc that will report if a passed-in Windows folder exists or not. While it avoids using the undocumented xp_fileexist system proc, it uses xp_cmdshell, which in many places is the first thing that gets disabled because of the obvious security ramifications. Still, it's a nice hack if you're working in the file system a lot.

IF OBJECT_ID('dbo.usp_FolderExist') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.usp_FolderExist
END
 
GO
 
CREATE PROCEDURE dbo.usp_FolderExist (@FullPathName NVARCHAR(255),
                                      @Exists       TINYINT OUTPUT)
AS
BEGIN
    SET NOCOUNT ON
   
    -- Remove any trailing slash.
    DECLARE @TrailingSlash NVARCHAR(255)
    SET @TrailingSlash = REVERSE(@FullPathName)
    
    IF '\' = LEFT(@TrailingSlash, 1) 
        SET @TrailingSlash = SUBSTRING(@TrailingSlash, 2, LEN(@FullPathName) + 1)
  
    SET @FullPathName = REVERSE(@TrailingSlash)
   
    -- Build and exec the command.
    DECLARE @Cmd NVARCHAR(4000)
    SET @Cmd = 'dir ' + @FullPathName
    SET @Exists = 0
    CREATE TABLE #dir (output NVARCHAR(4000))
 
    INSERT INTO #dir
        EXEC master..xp_cmdshell @Cmd
 
    IF EXISTS (SELECT * FROM #dir WHERE output LIKE '%' + @FullPathName + '%')
        SET @Exists = 1
      
    -- Done.
    DROP TABLE #dir
END
 

/* Test Code
 
set nocount on
declare @bExists int   set @bExists = 0
exec usp_FolderExist 'C:\Temp',  @bExists OUTPUT
print @bExists
exec usp_FolderExist 'C:\Temp\', @bExists OUTPUT
print @bExists
 
*/