Its possible to edit and query your SQL Servers drives, folders and files without enabling xp_Cmdshell. To do so you need to use the undocumented stored procedures which are detailed below, I hope the examples help.

/* 

Undocumented SQL Stored Procedures - Drive Folder and File Manipulation

	xp_fixeddrives
	xp_subdirs
	xp_dirtree
	xp_fileexist
	xp_create_subdir
	xp_delete_file
*/

/*
xp_fixeddrives
	returns free space in drives
*/
xp_fixeddrives


/*
xp_subdirs
	Lists all sub directories

xp_subdirs
	Directory { }
		'C:\\Program Files (x86)'
*/

-- Example 1 List folders in directory C:\Program Files
EXEC master..xp_subdirs 'C:\\Program Files'


/*
xp_dirtree
	Lists files and folders from a directory. Cant connect to network drives

xp_dirtree
	Directory { }
		Optional - Depth { 0 = All Subfolders 1 =  }
		Optional - File { 0 = Folders Only 1 = Folders and Files }

*/
-- Example 1 (bacic)
EXEC master.sys.xp_dirtree 'C:\Temp';

-- Example 2 (specific call only 2 subdirectories - filter to only folders)
EXEC master.sys.xp_dirtree 'C:\Temp',2,1;

-- Example 3 (xp_dirtree into temp table and filter results)
CREATE TABLE #dirtree
    (
      directory VARCHAR(255) ,
      depth INT ,
      [file] BINARY
    )
INSERT  INTO #dirtree
        ( directory ,
          depth ,
          [file]
        )
        EXEC master.sys.xp_dirtree 'Z:\SQLBackup', 1, 1;
SELECT  *
FROM    #dirtree
WHERE   [file] = 1
        AND directory LIKE '*.bak'

DROP TABLE #dirtree


/*
xp_fileexist
	Checks if a file exists (works for Files and Folders)
*/

-- Example 1 (Check if File Exists)
EXEC master.dbo.xp_fileexist 'C:\Temp\MaintenanceSolution.sql';

-- Example 2 (Check if Folder Exists)
EXEC master.dbo.xp_fileexist 'C:\SQLBackups'

-- Example 3 Use xp_fileexists to check if folder exists Out to Temp Table for use in creating folder
CREATE TABLE #fileExistsTemp
    (
      FileExists BIT ,
      FileIsADirectory BIT ,
      ParentDirectoryExists BIT
    ) 
INSERT  INTO #fileExistsTemp
        EXEC master..xp_fileexist 'C:\SQLBackups' 
IF EXISTS ( SELECT  FileIsADirectory
            FROM    #fileExistsTemp
            WHERE   FileIsADirectory = 1 )
    PRINT 'windows exists' 
ELSE
    PRINT 'not exists' 
DROP TABLE #fileExistsTemp


/*
xp_create_subdir
	Creates a folder
*/

-- Example 1 Basic
EXEC master.dbo.xp_create_subdir 'C:\SQLBackups'

-- Example 2 Use xp_fileexists to check if folder exists, If it doesnt Create it with xp_create_subdir
CREATE TABLE #fileExistsTemp
    (
      FileExists BIT ,
      FileIsADirectory BIT ,
      ParentDirectoryExists BIT
    ) 
INSERT  INTO #fileExistsTemp
        EXEC master..xp_fileexist 'C:\SQLBackups' 
IF EXISTS ( SELECT  FileIsADirectory
            FROM    #fileExistsTemp
            WHERE   FileIsADirectory = 1 )
    PRINT 'windows exists' 
ELSE
    PRINT 'not exists' 
EXEC master.dbo.xp_create_subdir 'C:\SQLBackups'
DROP TABLE #fileExistsTemp


/*
xp_delete_file
	Deletes files/files including files in subdirectories. Only deletes SQL backups, cant delete word files etc.

xp_delete_file
	File Type { 0 : Backup Files 1 : Report Files }
	Directory { 'C:\Backups\' -- Always end path with \}
	File Type { 'BAK' = *.BAK } 
		Optional - Date { delete files older than }
		Optional - Subfolder { 0 to ignore subfolders, 1 to delete files in subfolders }
*/
-- Example 1 Delete all files *.BAK in Z:\SQLBackups
EXEC xp_delete_file 0, 'Z:\SQLBackups','BAK';

-- EXAMPLE 2 Use variables
DECLARE @yesterday DATE
SET @yesterday = ( SELECT   CONVERT (DATE, GETDATE() - 1)
                 )
EXECUTE master.dbo.xp_delete_file 0,
    N'Z:\SQLBackups', N'bak',
    @yesterday, 1

-- Example 3 Delete all files older than 1 day in all subfolders
DECLARE @DeleteBefore DATETIME
SET @DeleteBefore = GETDATE() -3
EXEC xp_delete_file 0, 'C:\SQLBackups','BAK',@DeleteBefore , 0;




/*
xp_dirtree
	Lists files and folders from a directory.
	Cant connect to network drives

xp_dirtree
	Directory { }
		Optional - Depth { 0 = All Subfolders 1 =  }
		Optional - File { 0 = Folders Only 1 = Folders and Files }

NOTES:
	Getting Error: xp_delete_file() returned error 2, ‘The system cannot find the file specified.’
	Check file path is correct.
	Check SQL Service has permissions to delete in the folder.
*/
-- Example 1 (bacic)
EXEC master.sys.xp_dirtree 'C:\Temp';
-- Example 2 (specific call only 2 subdirectoryies - filter to only folders
EXEC master.sys.xp_dirtree 'C:\Temp', 2, 1;
-- Example 3 (xp_dirtree into temp table and filter results
CREATE TABLE #dirtree
    (
      directory VARCHAR(255) ,
      depth INT ,
      [file] BINARY
    )
INSERT  INTO #dirtree
        ( directory ,
          depth ,
          [file]
        )
        EXEC master.sys.xp_dirtree 'C:\Temp', 1, 1;

SELECT  *
FROM    #dirtree
WHERE   [file] = 1
        AND directory LIKE '*.bak'

DROP TABLE #dirtree

 

 

 

Advertisements