Declare @Dir VARCHAR(256) DECLARE @CMD VARCHAR(256) SET @Dir = 'C:\myImageFolder\' SET @CMD = 'DIR "'+@DIR+'" /A /S' drop table #tmp declare @media nvarchar(2048), @fn nvarchar(2048) DECLARE @CMD VARCHAR(256) set @media = 'D:\Filestore\media' SET @CMD = 'DIR "'+@media +'" /-C /S' CREATE TABLE #tmp (returnval NVARCHAR(500), rownum INT IDENTITY(1,1)) -- Populate Temp Table with the contents of the outfiles directory INSERT #tmp EXEC master..xp_cmdshell @cmd alter table #tmp add filename nvarchar(255) alter table #tmp add size int alter table #tmp add DocumentId int select convert(int,substring(returnval, 27,9)) size, substring(returnval, 37,999) name, * from #tmp where returnval like '__/__/____ __:__ %' --e.g. '17/11/2017 11:40 2645779 2017-11-17T114021.769Z001214 Band 4 Business Administrator Job Description_SM.pdf' update #tmp set filename = substring(returnval, 37,999) , size = convert(int,substring(returnval, 27,9)) where returnval like '__/__/____ __:__ %' update #tmp set documentid=d.documentid from #tmp, documents d where d.documentpath like '%' + #tmp.filename select filename, 'D:\MyStaffFile_Files\media\' + d.documentpath, s.assignmentid, size, d.documentid, createdby, createddate from #tmp inner join documents d on #tmp.documentid = d.documentid inner join sections s on d.sectionid=s.sectionid where size>1000000 order by CreatedBy, CreatedDate, Size desc --documentid desc
