get file names from folder

Question

 

Is it possible to write a TSQL statement to obtain the a list of files located in a specified directory? 

Also, if this is possible what additional information can be retrieved - in particular i'd like file size, author and date created (though I'd settle for just the file name).

Thanks

Answer 1

You can create a CLR FUNCTION that can be used in T-SQL code.

See:

Function -SQL CLR to Return File List
C# - http://blogs.msdn.com/stuartpa/archive/2005/07/21/441468.aspx
VB - http://blogs.msdn.com/sqlclr/archive/2005/05/20/420623.aspx

Answer 2

Another way to do this in SQL Server 2000/2005 is to use distributed queries. You can basically use the indexing services to index the directories and query those from SQL Server directly. See link below for some information:

 

http://msdn2.microsoft.com/en-gb/library/aa172902(SQL.80).aspx

 

Search in MSDN for more details on how to setup linked servers and queries against indexing services.

Answer 3

Try this:

create table #files (files varchar(max))
go

insert into #files
exec master.dbo.xp_cmdshell 'dir c:\' -- put here your directory name

select * from #files
go

Make sure that SQL Server account you're running is having access to the server ( not your local box !) file system.

Then you can delete garbage entries and use substring function to retrieve your information.

 

Answer 4

Mark Shvarts wrote:

exec master.dbo.xp_cmdshell 'dir c:\' -- put here your directory name

The issue with using xp_cmdshell is that it requires you to provide the user a relatively high level of security access, and has relatively unfettered access to all server resources, and you may not wish to make that functionality available to any users (other than administrators).

Answer 5

Hi Arnie,

You're right, xp_cmdshell assumes high level of server security, you have to use SQL Server configuration tools to set that security. But if he will write this procedure in VB or C# he will need to get same security anyway. My point is that without access to server resources he won't get this information, regardless of method he is using. 

Answer 6

However, if a procedure or function is created in .NET CLR, once created, permissions can be given to users to use that procedure or function, and they will be limited to only what that procedure or function can accomplish.

However, once permission is given to use xp_cmdshell, the users have virtually unfettered ability to do many things ( such as FORMAT C:\ ) other than the original intended action.

Using a .NET CLR procedure or function allows you to craft functionality that is severely constrained to the single task desired.

Answer 7

Actually same thing can be done by using proxy account ##xp_cmdshell_proxy_account##.

What this means is that users will be able to use xp_cmdshell only in context of a stored procedure where they are granted privileges.

Answer 8

You can use proxy accounts. But the xp_cmdshell approach has lot of drawbacks. Below are major ones.

 

1. It is hard to return table results efficiently. With SQLCLR, you can write TVFs that return the results. This allows great flexibility in terms of composing queries, reusability etc

2. xp_cmdshell does allow more things than just executing DIR. Granted this is governed by the permissions of the account under which it is being run. But there are only so many things you can protect at the OS level and allowing any arbitrary command to be executed via xp_cmdshell is a problem. With SQLCLR approach, the external access is restricted only to specific assemblies and operations are restricted to the assembly logic. You are prone to SQL injection attacks or worse but it is probably harder to exploit.