• triney

    Old Hand

    Points: 326

    I have just started using SQL SERVER and could use a little help. I am trying to purge unauthorized files and folders on a daily basis from a departmental share. I have a table(TAB_FILES) that contains FILE_NAME and FILE_PATH. I would like for all files that are not listed in the TAB_FILES.FILE_NAME to be deleted from the share. I also want to delete all folders that are not listed in the FILE_PATH to be deleted. Any help would be greatly appreciated.

  • nigelrivett


    Points: 5362

    Not sure that sql server is the best thing to do this from - maybe a client app would be better.

    You can execute any do commands using master..xp_cmdshell

    to get all the files in a directory (always use a variable for comannds and print it before execution to check).

    declare @cmd varchar(1000)

    create table #a (s varchar(1000))

    select @cmd = 'dir c:\dir\'

    insert #a

    exec master..xp_cmdshell @cmd

    (remember that the path is relative to the server and the sql server service must have permission on the directory).

    Once you have all files (look at #a and get rid of any non filename lines) you can remove the good files

    delete #a where not exists (select * from TAB_FILES t where t.FILE_NAME = #a.s)

    now you can delete

    declare @file varchar(1000)

    select @file = ''

    while @file < (select max(s from #a)


    select @file = min(s) from #a where s > @file

    select @cmd = 'del ' + @file

    exec master..xp_cmdshell @cmd -- important to run this as slect @cmd first to check


    You can do a similar thing with the directories (dir /s/B *.) but it is a bit more complicated - there is probably a better way of getting a list of all directories on the drive.

    Cursors never.
    DTS - only when needed and never to control.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply