CODEWITHSUNDEEP
sql-serversql-server-2008-r2filestream
UnDiUdin
UnDiUdin

Reputation: 15394

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

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.

Upvotes: 1

Views: 1488

Answers (1)

Giuseppe Menegoz
Giuseppe Menegoz

Reputation: 774

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]

Upvotes: 2

Related Questions