I'm building a process that reads a directory from the disk and stores the path and files details in a table.
Does anyone know of a xp (documented or undocumented) that will list all files in a given directory? I know I can use xp_cmdshell and do a DIR, but I'd prefer not to do it this way as end-users will be running the process.
I can get the directories easy enough using xp_dirtree, but I can't seem to find anything that'll list the files.
If there isn't one that lists files I'll just have to hack together a VBScript.
xp_dirtree combined with xp_getfiledetails may do the job for you.
eg. EXECUTE master.dbo.xp_dirtree N'C:\TEMP\', 1, 1
one of the columns returned will say whether the entry is a subdirectory or a file.
To get file details:
CREATE TABLE #FileInfo ( alt_name varchar(255) null, size_in_bytes int null, creation_date int null, creation_time int null, last_written_date int null, last_written_time int null, last_accessed_date int null, last_accessed_time int null, attributes int null )
INSERT #FileInfo EXEC master.dbo.xp_getfiledetails @Filename
Ah Ha ... where did you find out about the parameters for xp_dirtree?
well... um... er... I don't really know what those parms do. I guess the first says the depth you want to go down to. And the second, if non-null and non-zero, says that you want files as well as dirs.
I knew EM returned a list of files (when you choose an output file for a scheduled job step), so I just used profiler to capture what it was doing.