How to check whether file stream is enabled on the SQL Server Instance via T-SQL.

Problem: How to check whether file stream is enabled on the SQL Server Instance via T-SQL.

Solution:

sys.configurations

Under server- wide configuration catalog views, there is a system object view which stores a row for result for server wide configuration option value in system.

This kind of helps when you what state each configuration values or and gives a way to evaluate or change some decisions when a setting is turned or off.

To Know what a configuration option means, please go through all the list of  Server Configuration Options (SQL Server).

Example:

In this example, let’s see how can use configurations to find out the file stream service enabled.

Example 1:

select * from sys.configurations

where name ='filestream access level'

Results:

configuration_id	name	value	minimum	maximum	value_in_use	description	is_dynamic	is_advanced
1580	filestream access level	2	0	2	2	Sets the FILESTREAM access level	1	0

Example 2:

sp_configure 'filestream access level'

Results:

name	minimum	maximum	config_value	run_value
filestream access level	0	2	2	2