Recently I came across a situation where I need to check whether the directory is exists or not, in case if the directory does not exist, I have to create new one.

As a solution, I have created below script to fix the issue.

	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