datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to list all files in a directory?

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.

Warning article:

What are the security risks using cmdexec?

-- 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

The BEST 70-461 SQL Server 2012 Querying Exam Prep Book!

 
-- SQL reverse string function - charindex string function
SELECT
  FileName = REVERSE( LEFT(REVERSE(Line),CHARINDEX(' ',REVERSE(line))-1 ) ),
  CreateDate = LEFT(Line,10)
FROM #CommandShell
ORDER BY FileName
/* Results
 
FileName                            CreateDate
AdventureWorks2008.pdf              09/07/2018
AdventureWorks2008_Conceptual.pdf   09/07/2018
AdventureWorksDW.pdf                09/07/2018
AdventureWorksDW2008.pdf            09/07/2018
microsoft.lnk                       03/03/2019
*/
 
DROP TABLE #CommandShell

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 

 

Exam Prep 70-461
Exam 70-461