Get size of files using xp_cmdshell

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

 

Website by WNW Digital