How to Delete Directory from SQL Server?

Question

I have the following procedure which takes as parameter the name of database and the path and then creates a directory. My problem here I need override on the folder or delete it and then create a new one any idea of how to do such a thing

NOTE: this procedure creates in folder in depth of 0 and 1

ALTER PROCEDURE[dbo].[SP_CreateFolder]
    @P_PATH VARCHAR(100),
    @P_DBName VARCHAR(100)
AS
    DECLARE @DBName sysname
    DECLARE @DataPath nvarchar(500)
    DECLARE @LogPath nvarchar(500)
    DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)

    -- Initialize variables
    SET @DBName = @P_DBName
    SET @DataPath = @P_PATH

    -- @DataPath values
    INSERT INTO @DirTree(subdirectory, depth)
      EXEC master.sys.xp_dirtree @DataPath

    SELECT * FROM @DirTree

    --  Create the @DataPath directory
    --IF (SELECT depth from @DirTree)!= 1
    --EXEC master.dbo.xp_Delete_file @DBName
    IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
    BEGIN
        EXEC master.dbo.xp_create_subdir @DataPath
    END

    -- Remove all records from @DirTree
    DELETE FROM @DirTree

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Dec 21, 2016 at 21:10

SlimsGhost · Accepted Answer · 2019-09-20 13:20:42Z

3

First, the caveat: using T-SQL to operate on the file system is usually not a good idea, and there are a lot of risks assumed when doing it.

That said, you can use xp_cmdshell with the command-line syntax for whatever you want to do (deleting a folder, etc.), like this:

declare @rmdircmd nvarchar(280)
declare @dirname nvarchar(280)
set @dirname = '"C:\blah\blah\"'
set @rmdircmd = N'RMDIR ' + @dirname + ' /S /Q'
exec master.dbo.xp_cmdshell @rmdircmd, no_output

Good luck!

edited Sep 20, 2019 at 13:20

answered Dec 21, 2016 at 22:29

SlimsGhost

2,9091 gold badge12 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Geoff Griswald Over a year ago

If there are spaces in any of your folder names this will fail. You need to encapsulate your dirname variable in quotes: N'RMDIR "' + @dirname + '" /S /Q'

SlimsGhost Over a year ago

@JohnEddowes - good point, answer updated to include double quotes

Collectives™ on Stack Overflow

How to Delete Directory from SQL Server?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related