Catalog Views
select db_name(database_id) DatabaseName,non_transacted_access,non_transacted_access_desc,directory_name from sys.database_filestream_options select object_name(object_id),object_name(parent_object_id) from sys.filetable_system_defined_objects select * from sys.filetables select * from sys.tables where is_filetable=1
[Function] FileTableRootPath : Returns the root-level UNC path for a specific FileTable or for the current database.
USE <Database Name> GO select FileTableRootPath(null,null)
[Function] GetFileNamespacePath : Returns the UNC path for a file or directory in a FileTable.
USE SQL2016DB GO DECLARE @root varchar(100) DECLARE @fullPath varchar(1000); SELECT @root = FileTableRootPath(); SELECT @fullPath = @root + file_stream.GetFileNamespacePath() FROM [DocumentStore] WHERE Name = N'IMG_20140524_185328587.jpg'; select @fullPath SELECT file_stream.GetFileNamespacePath(1, Null) AS FilePath FROM [DocumentStore]
GetPathLocator : Returns the path locator ID value for the specified file or directory in a FileTable.
use <database name> go SELECT GetPathLocator('\\LENOVO-PC\SQL2016\Demo1\DocumentTable\IMG_20140518_195515101_HDR.jpg')
sp_kill_filestream_non_transacted_handles : Closes non-transactional file handles to FileTable data.
sp_kill_filestream_non_transacted_handles [[ @table_name = ] ‘table_name’, [[ @handle_id = ] @handle_id]] sp_kill_filestream_non_transacted_handles