23

I know this command will create a directory:

EXEC master.sys.xp_create_subdir 'C:\testing\'

But how do I check whether 'C:\testing\' exists?

IF EXISTS(...
Beryllium
  • 12,808
  • 10
  • 56
  • 86
JJ.
  • 9,580
  • 37
  • 116
  • 189
  • 12
    Disagree with this being closed as not constructive. The question is clear enough - I checked the help center to see what was legitimate reasons for closing and did not find this to match any of those reasons. The question, while perhaps not a fully code segment, is clear enough to understand that the question asks "How do I check for existance of a subdirectory and do stuff if it exists" – TDrudge Nov 27 '13 at 16:32
  • 2
    I agree. Now google brings me here as one of the first hits and the info is very concise and spot-on. – Gert Arnold Apr 04 '14 at 08:37
  • 3
    Yet another ludicrous "not constructive" closure. 7 upvotes for the answer, favourited twice, viewed 16,117 times. Yes, clearly a matter that is of no import. We need a process to vote OUT moderators for pulling this kind of stunt. – Alan K Feb 13 '18 at 00:20
  • Annoying that I can't provide this as a late answer... see this link https://www.sqlshack.com/file-validation-in-sql-server-with-xp_fileexist/ for 2017+ using `sys.dm_os_file_exists` if you need to do something inline in a function e.g. – downwitch Aug 03 '21 at 12:41

1 Answers1

26
 CREATE TABLE ResultSet (Directory varchar(200))

 INSERT INTO ResultSet
 EXEC master.dbo.xp_subdirs 'c:\'

 Select * FROM ResultSet where Directory = 'testing'

Will return a list of sub directories, you can then check the contents of the list.

Shiraz Bhaiji
  • 64,065
  • 34
  • 143
  • 252
  • how do I go about selecting from this table tho? i ran the command and i see the table but how do I go about querying it? – JJ. Dec 07 '12 at 15:29
  • 6
    I'd suggest using a temp table instead, so you don't end up with a static table floating around. CREATE TABLE #ResultSet (Directory varchar(200)) INSERT INTO #ResultSet EXEC master.dbo.xp_subdirs 'c:\' If Exists (Select * From #ResultSet where Directory = 'testing') Begin # Do stuff here End If you need to do this multiple times over a block of code, create the temp table at the very beginning of the code and then truncate it after each time you use it - slightly optimizes the code to avoid reallocation each time. – TDrudge Nov 27 '13 at 16:25
  • 4
    Use this below code: `code` DECLARE @BackupDestination nvarchar(500) = N'C:\testing\'; DECLARE @DirectoryExists int; EXEC master.dbo.xp_fileexist @BackupDestination, @DirectoryExists OUT; IF @DirectoryExists = 0 EXEC master.sys.xp_create_subdir @BackupDestination; – Mohsen Jul 09 '15 at 09:57
  • Do we know if this *master.dbo.xp_subdirs* feature also works for network mapped drives? When I tried on a network mapped drive, I got 'The system cannot find the path specified.' Thanks! – Jie Sep 28 '22 at 18:04
  • @Jie The reason for that is that the process identity of SQL Server does not have access to the network drive – Shiraz Bhaiji Sep 29 '22 at 08:56