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?

Answer 1

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.

Answer 2

CLR integration is also an option, if you're not comfortable with allowed xp_cmdshell being executed.

http://msdn.microsoft.com/en-us/library/ms345136%28SQL.90%29.aspx