Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to list all files and all directories in a directory using xp_cmdshell extended system stored procedure.

-- List all files in a directory - T-SQL parse string for date and filename
-- Microsoft SQL Server command shell statement - xp_cmdshell
DECLARE  @PathName       VARCHAR(256) ,
         @CMD            VARCHAR(512)
 
CREATE TABLE #CommandShell ( Line VARCHAR(512))
 
SET @PathName = 'F:\data\download\microsoft\'
 
SET @CMD = 'DIR ' + @PathName + ' /TC'
 
PRINT @CMD -- test & debug
-- DIR F:\data\download\microsoft /TC
 
-- MSSQL insert exec - insert table from stored procedure execution
INSERT INTO #CommandShell
EXEC MASTER..xp_cmdshell   @CMD
 
-- Delete lines not containing filename
DELETE
FROM   #CommandShell
WHERE  Line NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'
OR Line LIKE '%<DIR>%'
OR Line is null

Technical Job Search: CLICK HERE FOR HIGH PAID JOBS!

GO
------------
 
-- Get file list in specified directory (folder)
-- T-SQL command shell - insert exec
CREATE TABLE #FileList (
  Line VARCHAR(512))
DECLARE @Path varchar(256) = 'dir f:\data\'
DECLARE @Command varchar(1024) =  @Path+' /A-D  /B'
PRINT @Command
INSERT #FileList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #FileList WHERE  Line IS NULL
 
SELECT * FROM   #FileList
GO
DROP TABLE #FileList
GO
 
-- Get directory (subdirectory, folder) list in specified directory (folder)
CREATE TABLE #DirectoryList (
  Line VARCHAR(512))
DECLARE @Path varchar(256) = 'dir f:\data\'
DECLARE @Command varchar(1024) =  @Path+' /A-A  /B'
PRINT @Command
INSERT #DirectoryList
EXEC MASTER.dbo.xp_cmdshell @Command
DELETE #DirectoryList WHERE  Line IS NULL
 
SELECT * FROM   #DirectoryList
GO
DROP TABLE #DirectoryList
GO
------------

Related articles:

http://www.sqlusa.com/bestpractices2005/notepad/

xp_cmdshell (Transact-SQL)

Import multiple Files to SQL Server using T-SQL