Function sys.dm_os_enumerate_filesystem

While reviewing SQL Server vNext I came across the sys.dm_os_enumerate_filesystem function.
The function takes the following 2 parameters

  • @initial_directory nvarchar(255)
  • @search_pattern nvarchar(255)

It appears to be extremely powerful and currently outputs the following columns:

  • full_filesystem_path
  • parent_directory
  • file_or_directory_name
  • level
  • is_directory
  • is_read_only
  • is_system
  • is_hidden
  • has_integrity_stream
  • is_temporary
  • is_sparse
  • creation_time
  • last_access_time
  • last_write_time
  • size_in_bytes

Below is sample extract when using
SELECT * FROM sys.dm_os_enumerate_filesystem('c:\','*')


The command doesn’t appear to work 100% at this stage on Linux. I received the following info and then it errored:

Msg 3634, Level 16, State 1, Line 7
The operating system returned the error '0x80070002(The system cannot find the file specified.)' while attempting 'GetNextStream' on '(null)'.


Leave a Reply