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

10 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

Lily Charlie said...
This comment has been removed by a blog administrator.
blogs said...

I admire this article for the well-researched content and excellent wording. I got so involved in this material that I couldn’t stop reading. I am impressed with your work and skill. Thank you so much. Folder Lock For PC Zedge - Don't Spend time Seeking, Study Exactly Concerning PC Desktops Here password protect a folder in windows 7

Muhammad Hassan said...

Free File Protector PC Software Download - Don't Spend Time Seeking, Read through About PC Devices In this article alexandro

Happy Gambol said...

Nice Writing overall and you have published this content with deep message
Check My Work Too
happywheels.in
happy wheels game
fireboyandwatergirl.co
happy wheels

gunardi said...

You managed to hit the nail upon the top and also defined out the whole thing without having side effect , people could take a signal. Will likely be back to get more. Thanks
Tangki Panel
Tangki Fiberglass
Jual Septic Tank
Tangki Kimia
Jual Talang Fiber
Jual Rotameter
jual mesin ro
Jual Botol Plastik Agro

Jual Botol Plastik Murah

Aku Masih Disini said...


BORNEOPOKER.COM BANDAR POKER ONLINE SERTA BANDAR Q ONLINE TERPERCAYA DI INDONESIA

Vipqiuqiu99.com Agen Judi Domino Online Terpercaya di Indonesia
agen terpercaya

Kevin Dellinger said...

What's up it's me, I am also visiting this web site daily, this web site is in fact nice and the viewers are in fact sharing nice thoughts. Have a peek at this website Best Encryption Software Tips You Will Read This Year & The 3 Really Obvious Ways To How To Encrypt A Password For Free Better That You Ever Did

Kevin Dellinger said...

This is my first time pay a visit at here and i am actually happy to read Everthing at alone place. Read review: How To Lock Files

Post a Comment

Ads