Tag: List filenames in a folder

Cmd Shell, NON SQL

How to get list of filenames in a folder – Command Shell Script

There may be some situation where you want to find the list of all filenames whose patterns are like “*.txt”, “File*.csv”, “*.html”.., etc. In such scenario you can use the below command shell scripts to get the desired results which you are looking for.

1) To get the list of all filenames and folder names present in a folder “C:\testdata\”

cd "C:\testdata\"
dir /b /s

Output:-

2) To get all the filenames of pattern “*.txt” or “*.html” from folder “C:\testdata\” 

dir /b /s "*.txt"
dir /b /s "*.html"

Output:-

3) To get all the filenames of pattern “*.txt” from folder “C:\testdata\” and store the corresponding results in the text document

dir /b /s "*.txt" > output.txt
dir /b /s "*.txt" > "c:\testdata\test\outputs.txt"

Output:-

SCRIPT GALLERY, SQL

How to list file names in a folder in SQL Server

Here is a very small script to list the file names in a folder using SQL Server.

The below snippet has been useful for us to automate the restore process from a defined path(for large number of backup files). 


/**********************************************************************/
	DECLARE @COMMANDSHELL TABLE ( LINE VARCHAR(512)) 
	DECLARE  @CMD VARCHAR(512) ,@BACKUPFILEPATH NVARCHAR(256)
	SET @BACKUPFILEPATH = 'D:\DATABASES\BACKUPS\' 
	SET @CMD = 'DIR /B ' + @BACKUPFILEPATH +  ' /TC' 
				
	INSERT INTO @COMMANDSHELL EXEC MASTER..XP_CMDSHELL   @CMD 

	--Data Clean up 
	DELETE FROM   @COMMANDSHELL WHERE  LINE IS NULL

	SELECT* FROM @COMMANDSHELL A
/**********************************************************************/

Prerequisites: You may need to enable the xp_cmdshell using sp_configure as below:


--To Enable the advanced Options
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
RECONFIGURE WITH OVERRIDE

--To Enable XP_CmdShell 
EXEC SP_CONFIGURE 'XP_CMDSHELL',1
RECONFIGURE WITH OVERRIDE

--To Disable the advanced Options(by default)
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS',0
RECONFIGURE WITH OVERRIDE