Thursday, February 16, 2012

Listing Directory Contents using T-SQL

In many cases during the development of a sophisticated SQL Server Agent job, you might need to access the file system. For example you might want to list the contents of a specific directory and based on the output to proceed to other actions folllowing a series of steps that belong to a broader logic implemented via a SQL Server Agent job.

Don't worry, there's nothing stopping you from performing the above operation!

For such and similar purposes you can always use the extended stored procedure xp_cmdshell.

As described in SQL Server BoL, xp_cmdshell takes as a parameter a string and passes it to a Windows command shell for execution. Any output is returned as rows of text.

xp_cmdshell, by default, is disabled within a SQL Server instance and there is a good reason for this. When this extended stored procedure is enabled, it introduces a security risk that you should be aware of. As xp_cmdshell allows executing commands on the Operating System's level, you should be extremely careful with who has the right to execute this stored procedure. To this end, you should always use xp_cmd with caution.

OK, enough with the theory. Let's proceed with a practical example and some T-SQL!

--You can enable xp_cmdshell using the following T-SQL statement:
exec sp_configure 'xp_cmdshell',1

Then, if you want for example to list the contents of the directory "c:\tmp" you can do so by executing the following T-SQL statement:

-- You first create a temporary table for storing the contents of the directory.
CREATE TABLE #dirContents
contents NVARCHAR(255)

-- You then execute xp_cmdshell by using the DOS "dir" command.
-- You store the output to the temporary table created earlier.
-- The contents are stored as string expressions line-by-line.
-- Each empty line is a NULL.
INSERT INTO #dirContents
EXEC xp_cmdshell 'dir "c:\tmp"';

-- You can then scan the table for the output and analyze the data
-- using string manipulation techniques
FROM #dirContents

If you want to disable xp_cmdshell, you can do so as follows:

exec sp_configure 'xp_cmdshell',0

I hope you found the post useful!

Check out my latest eBook on SQL Server:
Tuning SQL Server - Ebook

Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (