Get contents of a directory/share to a table

Total article views: 120 | Views in the last 30 days: 3

 Rate this |   Join the discussion |  Briefcase

Following on from todays QOD (on xp_cmdshell permissions) I thought this might be useful.

Takes a directory or UNC path and returns the contents as a table. 

eg:
exec sp_getdir '\\mypc\c$'

returns:
datestring timestring directory   filesize    nameoffile
---------- ---------- ----------- ----------- ----------------------
18/07/2003 10:45      1           NULL        Documents and Settings
18/07/2003 11:02      1           NULL        Program Files
10/07/2003 17:03      1           NULL        WINNT

It handles the error mentioned in the QOD with a useful message.

eg:
exec sp_getdir '\\mypc\c$' (where I'm not sysadmin)

might return:
Current user's login is NOT a member of the sysadmin role
Non sysadmin executions of xp_cmdshell currently run as: MYDOMAIN\AUSER
You can change this with xp_sqlagent_proxy_account N'SET', , ,

Total article views: 120 | Views in the last 30 days: 3