List FILESTREAM databases in SQL Server

Problem: Received a support ticket requesting help with the FILESTREAM database for a SQL Server.  Was given only the server name.  This server had over 50 databases in the SQL Server instance.  Needed to quickly find which of these databases are FILESTREAM databases.

-- Need to be run on SQL 2012 or higher
DB_NAME(database_id) as "Database Name",
directory_name as "FileStream Directory"
FROM  sys.database_filestream_options
WHERE non_transacted_access_desc <> 'OFF';


Leave a Comment