question

I would like to fetch any filename from path in SQL server and result should show filename along with its location. I have written below script wherein @files is only fetching file name from different path. I would like to have their location as well. Location may be different and folder name is basically date in yyyymmdd format. Here I would like to fetch file which starts with ERR as prefix from 3 different folders which are 20170602, 20170603, 20170604. Please help me. declare @files table (Filename varchar(1000)) declare @foldername varchar(1000) = REPLACE(CONVERT(varchar(10), GETDATE()-3, 112), '/', '') declare @foldername2 varchar(1000) = REPLACE(CONVERT(varchar(10), GETDATE()-2, 112), '/', '') declare @foldername3 varchar(1000) = REPLACE(CONVERT(varchar(10), GETDATE()-1, 112), '/', '') declare @folderpath varchar(1000) declare @folderpath2 varchar(1000) declare @folderpath3 varchar(1000) set @folderpath = 'xp_cmdshell ''dir E:\Batch\Error_Logs\'+@foldername+ ' /b''' set @folderpath2 = 'xp_cmdshell ''dir E:\Batch\Error_Logs\'+@foldername2+ ' /b''' set @folderpath3 = 'xp_cmdshell ''dir E:\Batch\Error_Logs\'+@foldername3+ ' /b''' INSERT INTO @files exec (@folderpath) INSERT INTO @files exec (@folderpath2) INSERT INTO @files exec (@folderpath3) select * from @files where Filename like 'ERR_%'