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 sysadmin 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 1st parameter is a path to a directory.  The path must be accessible to SQL Server (the service account or the proxy account).

The 2nd parameter is a table name in which to insert the file/folder names.  This 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 3rd 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 4th 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 5th parameter determines whether files or folders are listed.  A value of zero (0) returns files and a value of one (1) returns folders.

This 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.