Check and Create a Folder Using T-SQL Code

> Check if Folder Exist or Not in our Desired Location Using System Stored Procedure

"master.dbo.xp_fileexist" And if not exists create Directoty Using Below T-SQL Code:

declare @chkdirectory as nvarchar(4000)

    declare @folder_exists as int     

    set @chkdirectory = 'C:\SQLDBPool\SQL\Articles' 

    declare @file_results table

    (file_exists int,

    file_is_a_directory int,

    parent_directory_exists int

    ) 

    insert into @file_results

    (file_exists, file_is_a_directory, parent_directory_exists)

    exec master.dbo.xp_fileexist @chkdirectory

     

    select @folder_exists = file_is_a_directory

    from @file_results     

    --script to create directory       

    if @folder_exists = 0

     begin

        print 'Directory is not exists, creating new one'

        EXECUTE master.dbo.xp_create_subdir @chkdirectory

        print @chkdirectory +  'created on' + @@servername

     end       

    else

    print 'Directory already exists'

GO