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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014



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

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.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems