T-SQL Query to get the list of files in a folder | SansSQL

Friday, February 15, 2013

T-SQL Query to get the list of files in a folder

Here is a T-SQL Query to list all the files in a folder. This uses a undocumented extended stored procedure to get the details.

DECLARE @Path nvarchar(500) = 'E:\Test' --Change the path

DECLARE @FindFile TABLE 
 (FileNames nvarchar(500)
  ,depth int
  ,isFile int)

INSERT INTO @FindFile 
EXEC xp_DirTree @Path,1,1

SELECT FileNames from @FindFile where isFile=1

2 comments:

Suresh said...

We can also use xp_cmdshell. But anyway, your code looks decent..

Anonymous said...

Don't believe you can set a local variable i.e. to declare and SET @Path in same statement.

Should be
DECLARE @Path nvarchar(500)
SET @Path = 'E:\Test --Change the path

DECLARE @FindFile TABLE
(FileNames nvarchar(500)
,depth int ,isFile int)

INSERT INTO @FindFile
EXEC xp_DirTree @Path,1,1
SELECT FileNames
FROM @FindFile WHERE isFile=1

Post a Comment