It is possible to get the content of a directory from T-SQL with the Extended Stored Procedure (XP) xp_dirtree. This only requires the SQL Server service account to have access to the directory and have browse rights.
Syntax:
EXECUTE master.dbo.xp_dirtree <Directory>, <Depth>, <IsFile>
<Directory> nvarchar(???): Full path (name) of directory. Can be UNC path. The value can end with '\' or not.
<Depth> int: Depth of list in directory.
<IsFile> int: Show if row is on a file. If the value in non-zero then the result will show if a row is in a file.
The result is a table with the three columns subdirectory, depth and file where
subdirectory is the name of the subdirectory or file in the given directory.
depth is the depth of the directory/file in the given directory. One ('1') is the root of the current directory.
file indicates if the directory is a file ('1') or not ('0'). If not a file then it is a directory.
Example:
EXECUTE master.dbo.xp_dirtree N'D:\MSSQL\Backup', 1, 1;
List (backup) files in folder.
Unfortunately this Extended Stored Procedure is not documentd by Microsoft. Like many other XP's...
Posts
