This Article Describe,
how to list all file names from a folder and sub-folders? or SQL Server Query to List Files
Most of the time as DBA we get request to restore multiple databases.
We can perform SQL Server database restore using SSMS or T-SQL Restore commands.
Doing this database restore task in SSMS, we need to restore database one by one by selecting backup file every time in SSMS
We get backup location where backup file placed, and we have to manually get file name and add it in restore script. This task is time consuming if no of databases need to be restored is high.
This SQL script will allow you to find all backup files name from folder. You can copy it to excel to generate database restoration script. This will save your time for script generation and also help you to avoid mistake in database restore as well.
This Script also help to get list of files from folder which can be used to copy files from one folder to another or delete old files. you can create PowerShell script based on filename and automate those tasks as well
If you want to Get filename from path string in SQL 2022
How to run Query in SQL Server SSMS for
how to list all file names from a folder and sub-folders? or SQL Server Query to List Files
Step 1 : Connect your SQL Server instance and open new query window in SSMS and enable SQLCMD mode in Query tab.
Step 2: Copy and paste below query in query window.
--SQL Server Query to List Files
DECLARE @Path nvarchar(500) = 'C:\Backup'
--Change the path
DECLARE @FindFile TABLE (FileNames nvarchar(500) ,depth int ,isFile int)
INSERT INTO @FindFile EXEC xp_DirTree @Path,1,1
SELECT FileNames from @FindFile where isFile=1
How to list all file names from a folder and sub-folders?
Step 3: Execute query and get all file name present in that folder.
1 thought on “How to list all file names from a folder and sub-folders? | SQL Server Query to List Files”