Use [My Personal Db]
go
begin tran
declare @dirmask varchar(30), @mydocdir varchar(255),
        @cmdstr varchar(255), @dir varchar(500),
        @skipfiles varchar(255), @backuptablename varchar(255)
select @mydocdir = 'C:\Documents and Settings\Umachandar Jayachand\My Documents',
        @dir = '',
        @skipfiles = '~exclude.txt~'
-- BACKUP before truncating...
SELECT @backuptablename = 'My Resource Files Backup (' +
                                CONVERT(varchar, CURRENT_TIMESTAMP, 120) + ')'
EXEC('SELECT * INTO [' + @backuptablename + '] FROM [My Resource Files]')
SELECT * INTO #Files FROM [My Resource Files] WHERE 1 = 0
ALTER TABLE #Files ADD DEFAULT ('.') FOR Directory
Insert #Files (Directory)
select distinct directory from webtechfiles

while(@dir is not null)
begin
        select @dir = min(Directory) from #Files where Directory > @dir
        select @cmdstr = 'dir /b/a-d "' + @dir + '"'

        insert #Files (Filename)
        exec master..xp_cmdshell @cmdstr
        
        update #Files set Directory = @dir where Directory = '.'
end
delete #Files
where charindex('~' + [FileName] + '~', @skipfiles) > 0

update #Files
set RelativePath = replace(Directory, @mydocdir, '.')

update #Files
set FileName = '<< ' + reverse(left(reverse(RelativePath),
                                charindex('\', reverse(RelativePath)) - 1)) + ' >>'
where [FileName] is null

-- select Directory, RelativePath, [FileName], Description from #Files
-- order by RelativePath, [FileName]

Update r
Set r.Directory = f.Directory, r.RelativePath = f.RelativePath
From [My Resource Files] r JOIN #Files f
On r.Filename = f.Filename 
Where r.Directory <> f.Directory

Insert [My Resource Files]
Select * From #Files f
Where Not Exists(Select * From [My Resource Files] r
                Where r.Directory + '\' + r.Filename = f.Directory + '\' + f.Filename)

Delete r
From [My Resource Files] r
Where Not Exists(Select * From #Files f
                Where r.Directory + '\' + r.Filename = f.Directory + '\' + f.Filename)

Drop Table #Files

select RelativePath, [Filename] from [My Resource Files] 
order by RelativePath, [FileName]
rollback
-- commit

This page was last updated on May 01, 2006 04:28 PM.