Using T-SQL to Query a File System Folder

Question

Is it possible to query a folder in TSQL, from SQL Management Studio, and return a list of file names? If so, how?

AdaTheDev · Accepted Answer · 2010-02-28 00:43:26Z

15

You can use xp_cmdshell.

Example:

EXECUTE master.dbo.xp_cmdshell 'DIR "C:\YourDirectory\" /A-D /B'

There's a good, full example with more options here.

answered Feb 28, 2010 at 0:43

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Faiz Over a year ago

I would disagree. This is like you using command line to bring information but not querying NTFS. And you cannot use any WHERE clause to say select only latest files (Modified Date filter). Good attempt though...

Thanos Markou Over a year ago

@Faiz The above query does exactly what OP asked though.

Uwe Keim · Accepted Answer · 2019-10-08 06:42:45Z

7

CLR integration is also an option, if you're not comfortable with allowing xp_cmdshell to be executed.

See MSDN.

edited Oct 8, 2019 at 6:42

Uwe Keim

40.9k61 gold badges193 silver badges309 bronze badges

answered Feb 28, 2010 at 1:32

ScottE

21.6k19 gold badges97 silver badges131 bronze badges

1 Comment

John K Over a year ago

+1 because I personally like the features offered by this mechanism - they have come in very useful to me for all kinds of tasks that are obtuse in TSQL. And +10 to Microsoft for creating this .NET integration feature in SQL Server.

Davin Studer · Accepted Answer · 2021-08-05 16:27:54Z

5

Years later now there is a DMF called dm_os_enumerate_filesystem that can do this. This was added with SQL 2017.

select * from sys.dm_os_enumerate_filesystem('c:\','*.*')

answered Aug 5, 2021 at 16:27

Davin Studer

1,6213 gold badges17 silver badges28 bronze badges

Collectives™ on Stack Overflow

Using T-SQL to Query a File System Folder

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related