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 SELECT DB_NAME(database_id) as "Database Name", directory_name as "FileStream Directory" FROM sys.database_filestream_options WHERE non_transacted_access_desc <> 'OFF';