CODEWITHSUNDEEP
sqlsql-serverdirectoryoverriding
Lanaa
Lanaa

Reputation: 159

How to Delete Directory from SQL Server?

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

Upvotes: 0

Views: 9647

Answers (1)

SlimsGhost
SlimsGhost

Reputation: 2909

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!

Upvotes: 3

Related Questions