Does file exist in current Folder using SQL Server?

Question

CREATE PROCEDURE SearchFile_InAllDirectories 
     @SearchFile VARCHAR(100)

DECLARE @BasePath VARCHAR(1000),
        @Path VARCHAR(1000),
        @FullPath VARCHAR(2000),
        @Id INT;

SET @SearchFile = 'test2019.txt'

CREATE TABLE tmp_BasePath 
(
    basePath VARCHAR(100)
);

INSERT INTO tmp_BasePath (basePath) 
VALUES ('\\Path1'), ('\\Path1\Images_5'),
        ('\\Path3\Images_4'), ('\\basketballfolder\2017_Images'),
        ('\\basketballfolder\2017_Images')

CREATE TABLE tmp_DirectoryTree 
(
     id INT IDENTITY(1,1),
     subdirectory VARCHAR(512),
     depth INT,
     isfile BIT,
     fullpath VARCHAR(500)
);

DECLARE basePath_results CURSOR FOR
    SELECT bp.basePath

OPEN basePath_results

FETCH NEXT FROM basePath_results into @BasePath

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO tmp_DirectoryTree (subdirectory, depth, isfile)
        EXEC master.sys.xp_dirtree @BasePath, 0, 1;

    FETCH NEXT FROM basePath_results INTO @Basepath
END

CLOSE basePath_results;
DEALLOCATE basePath_results;

END

I am creating a stored procedure that will check to see if the file passed in as a parameter, is located in one of the hard coded folders.

For example, if I pass in a file named "test2019.txt", the stored procedure should then check to see if that file exist in the folder. If yes, return true and return file path.

So essentially I just want to check if a file exist in current directory if yes give me back the full path.

Right now I am able to use a cursor to dynamically get the folder paths. Now just need a way to check to see if the file exist in the folder path, and return full path.

Please see code. I hope this makes sense. Thanks for help.

I am using SQL Server 2017.

Three letters....CLR. Doing this in straight t-sql is painful and slow. — Sean Lange
– Sean Lange, Commented Mar 19, 2019 at 13:59
Why do this is SQL Server in the first place? Surely doing so in the application would be a far easier, and better, way to do this. XY Problem? — Thom A
– Thom A ♦, Commented Mar 19, 2019 at 14:45
Thanks for the feed back. CLR sounds like a good idea. and i cannot build it in the application due to owners request. — Bakari Lewis
– Bakari Lewis, Commented Mar 19, 2019 at 15:03
i was looking for a different solution than xp_fileexist, although it does work, was looking for something a little more dynamic. If not i will use this — Bakari Lewis
– Bakari Lewis, Commented Mar 19, 2019 at 17:58

Kemal AL GAZZAH · Accepted Answer · 2019-03-19 18:43:09Z

I have another solution for you it uses the xp_cmdshell command to retrieve and store all the files with their full pathes inside a given folder

Please replace "répertoire de" by the English translation "folder of ", I am using a French Edition of Windows

**

--Kamel Gazzah
    --19/03/2019
    --Script to retrieve all the files in a a folder, inside all the sub 
     directoris

declare @folder as varchar(100)
-----------------------------------------
set @folder='d:\'
-----------------------------------------
declare @script as varchar(2000)
set @script='exec master..xp_cmdshell "dir '+@folder+'  /N /s"'
declare @mytab as table(id int identity(1,1),date_time datetime,folder int,filename varchar(1000),parent_folder varchar(200))
insert into @mytab(filename) exec(@script)
update @mytab set date_time= substring(filename,1,18) where date_time is null and isdate(substring(filename,1,18))=1
update @mytab set folder=1 where filename like '%répertoire de%' and folder is null
update @mytab set folder=0 where filename not like '%<DIR>%' and folder is null and date_time is not null
update @mytab set filename=replace(filename,'répertoire de ','') where folder=1
delete from @mytab where folder is null
update @mytab set parent_folder=t2.filename
--select t1.id,t1.folder,t1.filename,t2.filename
 from @mytab t1
outer apply (select top 1 filename from @mytab where id<t1.id  and folder=1 order by id desc) t2
where t1.folder=0
UPDATE @mytab SET FILENAME=substring(filename,37,len(filename)) WHERE FOLDER=0 
select id,replace(replace(parent_folder,'\',''),':',':\')+'\'+filename [Fullpath] from @mytab where folder=0

**

thanks this is working. Had to tweak the 'like' to accept a variable, but it works great. thanks for the help!

Collectives™ on Stack Overflow

Does file exist in current Folder using SQL Server?

1 Answer 1

Please replace "répertoire de" by the English translation "folder of ", I am using a French Edition of Windows

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Please replace "répertoire de" by the English translation "folder of ", I am using a French Edition of Windows

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related