2

I created a table that lists all the files in a directory using xp_dirtree. Now how do I keep this table updated when files are added or removed from this directory?

Here's some sample code of my table. Feel free to suggest simpler code as well.

create table ListDirectory
(
id int IDENTITY(1,1) PRIMARY KEY,
aFileName char(255),
extension char(10),
aFile char(255),
depth int,
isFile bit
)

insert ListDirectory(aFile, depth, isFile)
EXEC xp_dirtree 'c:\folder', 10, 1

update ListDirectory
SET aFileName = REVERSE(SUBSTRING(REVERSE(aFile), 
                CHARINDEX('.', REVERSE(aFile)) + 1, 999))

update ListDirectory                      
SET extension = REVERSE(
                       left(REVERSE(aFile), 
                       case when CHARINDEX('.', REVERSE(aFile) ) = 0 then LEN(REVERSE(aFile)) 
                       else CHARINDEX('.', REVERSE(aFile))-1 end)
                       )
Josh McGee
  • 75
  • 8
  • 1
    Well you could creat a sql job to truncate the table and reload it every day. Or use a batch job and windows scheduler to check for any changes to the directory and if they exists call a job that truncate and repopulates the table. I suppose it depends on what you are using this for and how often files and folders will be added. If you are using this very rarely in code, you may want to just store this in a temp table when you need to use it – S3S Jan 31 '17 at 03:57
  • @scsimon thanks for your comment and sorry for the late response (I finally got back to this project). This got me on the right path. I started with trying the SQL job, but I only have express on my server. I was able to create a batch file that will run a saved sql command that does as you suggest (truncate and repopulate) and add that to my task scheduler. – Josh McGee Mar 23 '17 at 17:17
  • Awesome @JoshMcGee. Not having Agent definitely makes things more difficult but luckily you seem to be well versed in batch scripts. – S3S Mar 23 '17 at 17:19
  • I wouldn't say well-versed; this is my first time (used another Stackoverflow answer to figure this out http://stackoverflow.com/questions/7201061/how-to-create-jobs-in-sql-server-express-edition) :) – Josh McGee Mar 23 '17 at 22:54

0 Answers0