There have been many posts in the newsgroups asking for a requirement to see directory content at SQL Server. So how can I get this information?

Answer:
You have two ways achieving this requirement. The easy way to get around this is to use the xp_cmdshell .
exec master..xp_cmdshell 'DIR /B /A-D c:\WinNt'
You can also try using undocumented features like xp_dirtree. Having said that, be careful while using such features as they may not be valid for different version of SQL Server.

create table #dir([filename] varchar(200) NOT NULL,
                  [depth] int NOT NULL,
                  [file] int NOT NULL)

 

insert #dir exec master.dbo.xp_dirtree 'c:\WinNT',1,1

 

select [filename] from #dir where [file]=1 and [depth]=1

 

drop table #dir

Adding to the above requirement. How can I also get to see the size and the Last Changed by Option ... You can surely use the below DIR switch to get the same. But there are lots of verbrose ...
exec master..xp_cmdshell 'DIR /A-D c:\WinNt'