SQL Query to Get List of files in a Directory and Subdirectory in Microsoft SQL Server

Hi everyone in this post I will explain - How to get all file names present in a directory and a sub directory in SQL Server by using "dir" dos command and Stored Procedure xp_cmdshell.

To understand the query, first we need to have complete understanding of dir command. Lets explore the dir command first.

If you have complete understanding of dir command then you can skip Step 1

Also I will using the stored procedure xp_cmdshell To execute the DOS command from SQL Server.

Step 1) Understanding DOS Command dir.

DOS Command to list files in a directory are - dir "C:\Log Files"

Output Analysis – As you can see only files and Folders of particular directory shown Also Hidden files are not show.

Display Parameters - /A - Displays files with specified attributes.                   -  Prefix meaning not      Attributes   D  Directories                R  Read-only files                   H  Hidden files               A  Files ready for archiving                   S  System files               /B - Uses bare format (no heading information or summary). /S - Displays files in specified directory and all subdirectories. Command - dir "C:\Log Files" /A-D Displays Only Files in the Directory with Header information. Command - dir "C:\Log Files" /A-D-H Displays Only Files in the Directory and Removes Hidden Files but Header information will be displayed (Note - 27-Jan-2017.log is hidden file) Command - dir "C:\Log Files" /A-D /B Displays Only Files in the Directory without any Header information Command - dir "C:\Log Files" /A-D /B /S Displays Only Files in the Directory and Sub Directory without any Header information Output -

Sort Parameters /O – Used to specify the Sort parameter -  Prefix to reverse order N  By name (alphabetic)       S  By size (largest first) E  By extension (alphabetic)  D  By date/time (Newest first) G  Group directories first     Command - dir "C:\Log Files" /A-D /B /S /O-S Sorts the files with Size – Largest first Command - dir "C:\Log Files" /A-D /B /S /O-D Sorts the files with Size – Newest first Output -

Step 2) Sql Query without formatting any output from dir dos command

Syntax –

xp_cmdshell { 'command_string' } [ , no_output ] 

Parameters –

command_string is varchar(8000) or nvarchar(4000), is the command to be executed in the DOS Shell

[no_output ] - Optional Parameter

Sql query 1 – Simple Sql Query without any Formatting the output obtained from dir command

--DELETE IF STORED PROCEDURE EXISTS IF OBJECT_ID (N'dbo.sp_GetFilesInFolder') IS NOT NULL    DROP PROCEDURE dbo.sp_GetFilesInFolder GO --CREATE THE PROCEDURE CREATE PROCEDURE dbo.sp_GetFilesInFolder @FileDirectory VARCHAR(2000) AS BEGIN       --CREATE TEMP TABLE       Create TABLE #myfiles             (ID BIGINT IDENTITY(1,1) PRIMARY KEY,             FullPath VARCHAR(2000))       DECLARE @CommandLine VARCHAR(4000)       -- DOS COMMAND WE ARE TRYING TO EXECUTE - dir "C:\Log Files"       SELECT @CommandLine ='dir "' + @FileDirectory+'"'  ;       --GET ALL FILES INTO TEMP TABLE #myfiles -       --SIMILARLY CAN STORE IN PERMANENT TABLE AS WELL       INSERT INTO #myfiles (FullPath)       EXECUTE xp_cmdshell @CommandLine       SELECT fullpath FROM #myfiles END

Executing EXEC sp_GetFilesInFolder 'C:\Log Files'

Output Analysis – As you can see that all the information that is displayed in the command Prompt is captured in the SQL Table.

 

  Step 3) Complete Sql Query with formatting and sorting to display all files in a folder

 

Sql Query 2 – Complete Query displaying all files present inside a Directory and Sub Directory after formatting the output obtained from dir command, sorted based on modified date

--DELETE IF STORED PROCEDURE EXISTS IF OBJECT_ID (N'dbo.sp_GetFilesInFolder') IS NOT NULL    DROP PROCEDURE dbo.sp_GetFilesInFolder GO --CREATE THE PROCEDURE CREATE PROCEDURE dbo.sp_GetFilesInFolder @FileDirectory VARCHAR(80) AS BEGIN       --CREATE TEMP TABLE       Create TABLE #myfiles             (ID BIGINT IDENTITY(1,1) PRIMARY KEY,             FullPath VARCHAR(2000))       DECLARE @CommandLine VARCHAR(4000)       DECLARE @DisplayOption VARCHAR(80) = '/A-D /B /S'       DECLARE @SortOrder VARCHAR(80) = '/O-D'       --DOS COMMAND TRYING TO EXECUTE - dir "C:\Log Files" /A-D /B /S /O-D       SELECT @CommandLine ='dir "' + @FileDirectory+'" ' + @DisplayOption + ' ' + @SortOrder   ;             --GET ALL FILES INTO TEMP TABLE #myfiles -       --SIMILARLY CAN STORE IN PERMANENT TABLE AS WELL       INSERT INTO #myfiles (FullPath)       EXECUTE xp_cmdshell @CommandLine       --LAST ROWSET WILL BE NULL HENCE DELETE THE NULL RESULT       DELETE FROM #myfiles WHERE fullpath IS NULL OR fullpath='File Not Found'             SELECT fullpath FROM #myfiles       END

Executing EXEC sp_GetFilesInFolder 'C:\Log Files'

Output Analysis – As you can see only the necessary files are displayed with sorted order

More Interesting posts -

Difference between SET and SELECT Statements in Microsoft SQL Server
http://pnsoftwarestudies.blogspot.in/2015/12/difference-between-set-and-select.html

Using SQL Profiler
http://pnsoftwarestudies.blogspot.in/2015/07/how-to-check-or-trace-select-statements.html