query help- list path (not filename) for all datfiles

• NJ-DBA

  SSChampion

  Points: 13834

  More actions

  July 12, 2011 at 8:00 am

  #241336

  working a query to list all paths which contain data or log files. starting with something like:

  select physical_name from master.sys.master_files

  but want to only include the path, not the file name, and only list each path once...

  so, everything left of the rightmost "\" should exclude filenames, and maybe a distinct in there...

  but cant figure out how to say "everything left of the rightmost \"

  any help would be appreciated.

• NJ-DBA

  SSChampion

  Points: 13834

  More actions

  July 12, 2011 at 8:16 am

  #1351606

  as often happens, I figure it out shortly after posting:

  select distinct (LEFT(physical_name, (LEN(physical_name)-(CHARINDEX('\', REVERSE(physical_name)) - 1)))) as path

  FROM master.sys.master_files