This is a quick post to show some of the functions available to you for FileTables and FILESTREAMING. These are mainly to show information on the SQL instance and FileTables. All the scripts use the Documents FileTable in my DemoDB I created in my earlier posts.

You probably first want to check if FILESTREAMING is enabled on your SQL instance. You can check the instance properties from SSMS, look at the FILESTREAM tab in SQL Configuration Manager, or check via T-SQL.

-- Value set in Configuration Manager
SELECT SERVERPROPERTY('FilestreamConfiguredLevel');

-- Actual value
SELECT SERVERPROPERTY('FilestreamEffectiveLevel');

-- Get the share name
SELECT SERVERPROPERTY('FilestreamShareName');

Next you probably want to find which databases are using FILESTREAMING. Query sys.database.filestream_options to see which databases have filestream enabled. There’s information on the FILESTREAM filegroup in sys.filegroups. Even better is looking at sys.database_files

-- Find all FILESTREAM enabled databases
SELECT d.Name AS Database_Name, dfo.non_transacted_access, dfo.non_transacted_access_desc, dfo.directory_name
FROM sys.database_filestream_options AS dfo
INNER JOIN sys.databases AS d
ON dfo.database_id = d.database_id;

-- Check  Filestream filegroup
USE DemoDB;

SELECT *
FROM sys.filegroups
WHERE type = 'FD';

SELECT *
FROM sys.database_files;

OK, you’ve found which databases (if any) are using FILESTREAMING. But it would probably be helpful to find which tables the FILESTREAM data is in, and if any of them are FileTables.

-- Find FILESTREAM tables
SELECT * FROM sys.tables
WHERE filestream_data_space_id IS NOT NULL;

-- Find FILESTREAM columns
SELECT OBJECT_NAME(object_id) AS TableName, name AS ColumnName
FROM sys.columns
WHERE is_filestream = 1;

-- Find FileTables
SELECT * FROM sys.tables
WHERE is_filetable = 1;

There’s still more. The FileTableRootPath function returns the UNC path of the database share. If you use the FileTable name as a parameter it returns the path for that FileTable. And if you want the the full path for each file in the FileTable use the GetFileNamespacePath function. By default GetFileNamespacePath returns the relative path. If you want the full UNC path use 1 as the parameter. There’s also an optional parameter to convert the path to NetBios (the default), to use the fully qualified path, or not to convert the path at all.

--Full Root Path
SELECT FileTableRootPath ('dbo.documents') AS SharePath

-- Options for columnname.GetFileNamespacePath
--  is_full_path - returns relative (0 default) or full UNC path (1)
--  @option - converts path to NetBios (0 default), no conversion (1), or complete path (2)

--Relative Path
SELECT file_stream.GetFileNamespacePath() AS FileLocation
FROM dbo.documents;

--Full Path
SELECT file_stream.GetFileNamespacePath(1) AS FileLocation
FROM dbo.documents;

-- Complete path
SELECT file_stream.GetFileNamespacePath(1, 2) AS FileLocation
FROM dbo.documents;