In this article, we’ll talk about 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 following 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 MS Docs, 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.
Example of Listing Directory Contents using T-SQL and xp_cmdshell
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; GO RECONFIGURE WITH override; GO
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 SELECT * FROM #dirContents;
If you want to disable xp_cmdshell, you can do so as follows:
exec sp_configure 'xp_cmdshell',0; GO RECONFIGURE WITH override; GO
I hope you found the post useful!
Learn more tips like this!
Enroll to our Online Course!
Check our online course titled “Essential SQL Server Development Tips for SQL Developers” (special limited-time discount included in link).
Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals (SQL Database for Beginners)
- Essential SQL Server Development Tips for SQL Developers (New)
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners: Windows Forms (C#)
- Introduction to SQL Server Machine Learning Services
- Introduction to Azure SQL Database
- SQL Server 2019: What’s New
- Entity Framework: Getting Started (Complete Beginners Guide)
- How to Import and Export Data in SQL Server
- Get Started with SQL Server in 30 Minutes
- A Guide on How to Start and Monetize a Successful Blog
Related SQL Server Development Articles:
- Error converting data type varchar to float
- Row Constructors in SQL Server 2008
- The TIME data type in SQL Server 2008 (and later)
- Error converting data type varchar to numeric
- The set identity_insert Command in SQL Server
- Handling NULL Character x00 when Exporting to File Using BCP
- The Net.Tcp Port Sharing Service service on Local Computer started and then stopped
- …more SQL Server development articles
Subscribe to our newsletter and stay up to date!
Check out our latest software releases!
Check out Artemakis’s eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou, a distinguished Senior Database and Software Architect, brings over 20 years of expertise to the IT industry. A Certified Database, Cloud, and AI professional, he earned the Microsoft Data Platform MVP title for nine consecutive years (2009-2018). As the founder of SQLNetHub and GnoelixiAI Hub, Artemakis is dedicated to sharing his knowledge and democratizing education on various fields such as: Databases, Cloud, AI, and Software Development. His commitment to simplicity and knowledge sharing defines his impactful presence in the tech community.