How to query the File Name of the Filestream Filegroup in a database?

Question

In sys.filegroups i see all the filegroups (main and filestram9 of my database, in particular this query gives me the name of the filegroup:

select  name from sys.filegroups where type = 'FD'

I would like to get the Filename of that filegroup, for a filestream filegroup the filename is the physical path on disc in which filestream data is stored and it is visible from management studio by selecting the database properties and then navigating to the File section.

Answer 1

The follolwing query should give you the value you are looking for

DECLARE @full_path VARCHAR(1000)
SET @full_path = 
(SELECT physical_name 
 FROM sys.master_files 
 WHERE name = 
 (SELECT  name 
  FROM sys.filegroups 
  WHERE type = 'FD'))

SELECT LEFT(@full_path,LEN(@full_path) - 
charindex('\',reverse(@full_path),1) + 1) [FileStreamPath]