This is the second in a series of tips that present SQL code for a variety of utility stored procedures. In the last tip, A simple stored procedure to list database objects by selected type(s), we explained that these stored procedures are primarily for administrative purposes, and they are designed to be installed as system stored procedures. This tip continues the system stored procedure series with another simple routine.
The SQL code in Listing 1 creates a system stored procedure named sp_ListFiles. The routine returns a filtered list of files found in a specified directory. The sp_ListFiles stored procedure uses the xp_cmdshell extended stored procedure that comes with SQL Server.
There are security implications with using that tool. Normally, only a systems administrator can execute xp_cmdshell, but others can be granted permission. The SQL Agent Proxy Account is another way to control access to the potentially dangerous tool. See BOL for details. The SQL Server service account (or the SQL Agent proxy account) must be able to read the files in the specified directory. If the specified directory is on a network share, the account may need to be a domain account.
The sp_ListFiles stored procedure accepts five parameters. Only the first one is required.
The first parameter is a path to a directory. The path must be accessible to SQL Server (the service account or the proxy account).
The second parameter is a table name in which to insert the file/folder names. It can be a normal user table or a temporary table. If no table name is provided, the list is returned as a result set.
The third parameter is a filter for including certain names. Each name is compared to the filter using a LIKE operator, so wildcards are acceptable. For example, the value "%.doc" would include all Word documents.
The fourth parameter is a filter for excluding certain names. Each name is compared to the filter using a NOT LIKE operator, so wildcards are acceptable.
The fifth parameter determines whether files or folders are listed. A value of zero (0) returns files and a value of one (1) returns folders.
The following example lists the folders within the Program Files folder on the SQL Server box:
EXECUTE sp_ListFiles 'C:\Program Files\',NULL,NULL,NULL,1
The list of files returned by sp_ListFiles may consist of files that need to be imported into the database. The BULK INSERT statement works nicely for this purpose, but it does not accept a variable for the parameter that specifies the file to import. Because of this limitation it's often convenient to execute the BULK INSERT statement using dynamic SQL so the file to import can be determined on the fly.
The SQL code in Listing 2 demonstrates this by returning the contents of a README file (assuming a typical installation of SQL Server). The lines of the file are not necessarily returned in order in this example, but if an order not supported by the data itself is required it can be accomplished with an identity column in the temporary table and a format file.
I hope you find this system stored procedure to be useful.
Click for the stored procedure: sp_ListFiles
About the author: Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has more than 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Walker is a software developer, database developer, database administrator and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.
Do you have comments on this tip? Let us know.
