T-SQL to find Database name and if Filestream is enabled for specific Database

Question

Please help me on below query .

NOTE : Please excuse my programming skills , the code is of mistakes .

I want the output like Database name in one column and the FileStream enabled or disabled in another column . Just 2 columns I am trying out for . Thanks

To find the Database name and if FileStream is enabled for the Database .

declare @cmd varchar(500)
set @cmd='USE ? 
if exists(
 select name, Filestream from sys.database_files where type_desc='FILESTREAM')
 CASE type_desc WHEN 'FILESTREAM' THEN 'Filestream Configured For Datbase' ELSE 'Filestream NOT Configured For Datbase'
PRINT 'Filestream Configured For Datbase'
else
 print 'Filestream Not configured for database' 
inner join sys.databases where name not in (''master'', ''tempdb'', ''model'', ''msdb'')')
exec sp_MSforeachdb @cmd 

Answer 1

Reference:

• Enable and configure FILESTREAM
• sys.master_files (Transact-SQL)

I would rephrase you question to find databases that are using FILESTREAM. Because you can only enable FILESTREAM at an instance level in order to use it in any user database.

To find all the files related to FILESTREAM or database name you can use this query.

SELECT db.NAME       AS DBName, 
       type_desc     AS FileType, 
       physical_name AS Location 
FROM   sys.master_files mf 
       INNER JOIN sys.databases db 
               ON db.database_id = mf.database_id 
WHERE  type_desc = 'FILESTREAM' 

Answer 2

Due to the fact that sp_MSforeachdb has known problems, I opted for Aaron Bertrand's sp_foreachdb stored procedure to query each database. I modified one of my local databases (Test) to enable FILESTREAM to see if my query returned the correct results (which it did in my case) - see if this returns the information you want.

--Temp table to hold the results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE [dbo].[#Results];

CREATE TABLE [dbo].[#Results] (
    [DBNAME] SYSNAME NULL
    ,[FileStreamDescription] VARCHAR(max) NOT NULL
    )

--Insert into #Results for each database
execute master.dbo.sp_foreachdb 'INSERT INTO #Results SELECT ''?'' as DBNAME,
 CASE 
    WHEN EXISTS (SELECT * FROM 
        sys.database_filestream_options dfo join 
        sys.databases d 
            on dfo.database_id = d.database_id 
            WHERE QUOTENAME(d.name) = ''?'' and dfo.NON_TRANSACTED_ACCESS <> 0) 
        THEN ''Filestream Configured For Database'' 
        ELSE ''Filestream NOT Configured For Datbase'' 
    END
where ''?'' NOT in (''[master]'', ''[tempdb]'', ''[model]'', ''[msdb]'')
';

--Query the #Results table
SELECT * FROM #Results order by DBNAME;

---

 DBNAME               | FileStreamDescription                 |
|----------------------|---------------------------------------|
| [AdventureWorks2014] | Filestream NOT Configured For Datbase |
| [foo]                | Filestream NOT Configured For Datbase |
| [ReadingDBLog]       | Filestream NOT Configured For Datbase |
| [Test]               | Filestream Configured For Database    |
| [zzz]                | Filestream NOT Configured For Datbase |