T-SQL script to delete files

Many a times need arises to access/modify windows folder structures from SQL server. Though SQL Server is not meant for this purpose, however here is a T-SQL script to delete files from SQL Server.

One way is to delete the file by running the DEL DOS command via xp_cmdshell extended procedure.

-- enable xp_cmdshell
sp_configure 'xp_cmdshell',1
sp_configure 'xp_cmdshell'
-- run del dos command to delete a file/folder
xp_cmdshell 'DEL E:\Deleted\del01.txt'

The above script first checks that xp_cmdshell is enabled and then deletes the file by executing DEL DOS command.

Another way is to use Ole Automation procedures as shown below.

-- using OLE Automation Procedures 
exec sp_configure
-- enable Ole Automation Procedures
sp_configure 'Ole Automation Procedures', 1
DECLARE @Filehandle int

-- create a file system object
EXEC sp_OACreate 'Scripting.FileSystemObject', @Filehandle OUTPUT
-- delete file 
EXEC sp_OAMethod @Filehandle, 'DeleteFile', NULL, 'E:\Deleted\del01.txt'
-- memory cleanup
EXEC sp_OADestroy @Filehandle

The above query first checks that Ole Automation procedures are enabled or not using sp_configure procedure, if not it enables it. It then creates a FileSystemObject and then calls sp_OAMethod with the @filehandle, DeleteFile and file name to delete a particular file.  The sp_OADestroy is to clean up the memory used by the unused objects.

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

One Comment on “T-SQL script to delete files”

  1. Hi,
    Can we delete Shared folder(Which is there in another machine, but i have full path) files using this command.
    Can you please suggest me how to delete the files which are there in shared folder.

    Naga Raju G

Leave a Reply

Your email address will not be published. Required fields are marked *