SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


List Directory Files using T-SQL xp_cmdShell Stored Procedure

Using SQL xp_cmdShell stored procedure to list directory files and display contents as a file list is possible for Transact-SQL developers by creating a short T-SQL script as shown in this SQL tutorial.

Besides using SQL CLR to list files in directory, T-SQL can be also used by developers for the same task to list files in a directory.

Please note that before using xp_cmdshell extended procedure on a SQL Server instance, it should be enabled on that server. Database administrators and SQL developers can refer to following tutorial for more detail on how to enable xp_cmdshell in SQL Server using sp_configure

Here is example SQL codes showing how to list directory files in a file folder using sql xp_cmdshell procedure.

CREATE TABLE dirList (
 id int identity(1,1),
 line nvarchar(1000)
)
GO;

INSERT INTO dirList (line) EXEC xp_cmdshell 'dir C:\SQLDatabases'

SELECT * FROM dirList;
WITH CTE AS (
 SELECT
  id,
  SUBSTRING(line,1,17) [date],
  SUBSTRING(line,18,19) sizeordir,
  SUBSTRING(line,37,100) name
 FROM dirList
 WHERE id > (
  SELECT MIN(id) FROM dirList WHERE line LIKE '%<DIR>%..%'
 ) AND id < (SELECT MAX(id) - 2 FROM dirList)
)
SELECT
 id,
 [date],
 isDirectory = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 1 ELSE 0 END,
 isFile = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 0 ELSE 1 END,
 name
FROM cte
Code

Above T-SQL script can be used to list files in directory using SQL code.
The returned isDirectory column and isFile column can be used to distinguish files and folders within the target file folder.

list directory files using SQL xp_cmdshell procedure
SQL Server xp_cmdshell procedure used to list files and folders in a target file folder

By using recursive SQL Select statements, it is possible to list all files and all folders under the root C drive.
Again recursive SQL statement will enable to get the directory structure of the target file folder or disk drive given as parameter in the T-SQL xp_cmdshell statement.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.