Where does your FILESTREAM data live?

This is hopefully just a short one but follows up on a previous post about using FILESTREAM with Entity Framework.

After implementing the solution almost as posted we ran into some problems on the environment it was being deployed to (for reference, do NOT disable netBIOS!).  Whilst investigating these issues we needed to figure out where on the server the FILESTREAM data was being stored.  Looking around the internet I found many posts about getting the path name from a SELECT statement but that's useless if you want to know "my data is at C:\<path>" because you want to check disk space, permissions etc...  Not having the DBA who installed available wasn't useful either and there doesn't appear to be a way to get this information back from SQL Server Management Studio.

But, there is a way to find it from the system tables.  So I put together a SQL statement which pulls the information out.  It worked for me but you may want to tweak it to give you the information you want, to filter stuff out and so on.

	SELECT t.name AS 'table',
	c.name AS 'column',
	fg.name AS 'filegroup_name',
	dbf.type_desc AS 'type_description',
	dbf.physical_name AS 'physical_location'
	FROM sys.filegroups fg
	INNER JOIN sys.database_files dbf
	ON fg.data_space_id = dbf.data_space_id
	INNER JOIN sys.tables t
	ON fg.data_space_id = t.filestream_data_space_id
	INNER JOIN sys.columns c
	ON t.object_id = c.object_id
	AND c.is_filestream = 1

view raw FileStreamLocations.sql hosted with ❤ by GitHub