Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Verify Directory Exists and Create if Necessary

By dlineberry, 2005/03/24

This script will check to see if a directory exists and if it does not then it will create the directory. I did not create this as a stored procedure but it certainly could be. I tend to just place this as one of my job steps so I can create a backup file of an import file. I also run another script just like this one immediately afterwards but it looks to verify the month folder exists and creates it inside this year folder (both could easily be run together). And finally I run a script to move and rename my import file to the directorys verified and/or created for the current year and month.

--	Create By:	Doug Lineberry
--	Create On:	2005-03-07
--	Purpose:	This script will check to see if a directory exists and if it does not then it will create the directory.

declare @year Char(4)
declare @cmd sysname
declare @dir varchar(200)
declare @direxists int
select @year = DATEPART(YY, GETDATE())
set @dir = 'c:\Temp\'

create table GetDirDetails_temp
(subdirectory varchar(100),depth int)

insert into GetDirDetails_temp
(subdirectory, depth)
exec master..xp_dirtree @dir

set @direxists = (select count(*) from GetDirDetails_temp where subdirectory = @year and depth = 1)

begin
-- * Check if direxists val 	= 1
if 		@direxists 	= 0
	begin
		Raiserror('Directory of Current Year Does Not Exist',16,1) with seterror
		set @cmd = 'mkdir ' + @dir + @year + '\'
		exec master..xp_cmdshell @cmd, no_output
	end
else
	print 'Directory of Current Year Does Exist'
end
drop table GetDirDetails_temp