FileTableRootPath (Transact-SQL)

  • Article

Returns the root-level UNC path for a specific FileTable or for the current database.

Syntax

FileTableRootPath ( [ ‘[schema_name.]FileTable_name’ ], @option )

Arguments

  • FileTable_name
    The name of the FileTable. FileTable_name is of type nvarchar. This is an optional parameter. The default value is the current database. Specifying schema_name is also optional. You can pass NULL for FileTable_name to use the default parameter value

  • @option
    An integer expression that defines how the server component of the path should be formatted. @option can have one of the following values:

    Value

    Description

    0

    Returns the server name converted to NetBIOS format, for example:

    \\SERVERNAME\MSSQLSERVER\MyDocumentDB

    This is the default value.

    1

    Returns the server name without conversion, for example:

    \\ServerName\MSSQLSERVER\MyDocumentDB

    2

    Returns the complete server path, for example:

    \\ServerName.MyDomain.com\MSSQLSERVER\MyDocumentDB

Return Type

nvarchar(4000)

When the database belongs to an AlwaysOn availability group, then the FileTableRootPath function returns the virtual network name (VNN) instead of the computer name.

General Remarks

The FileTableRootPath function returns NULL when one of the following conditions is true:

  • The value of FileTable_name is not valid.

  • The caller does not have sufficient permission to reference the specified table or the current database.

  • The FILESTREAM option of database_directory is not set for the current database.

For more information, see Work with Directories and Paths in FileTables.

Best Practices

To keep code and applications independent of the current computer and database, avoid writing code that relies on absolute file paths. Instead, get the complete path for a file at run time by using the FileTableRootPath and GetFileNamespacePath functions together, as shown in the following example. By default, the GetFileNamespacePath function returns the relative path of the file under the root path for the database.

USE MyDocumentDB;

@root varchar(100)
SELECT @root = FileTableRootPath();
@fullPath = varchar(1000);

SELECT @fullPath = @root + file_stream.GetFileNamespacePath()
FROM DocumentStore
WHERE Name = N’document.docx’;

Security

Permissions

The FileTableRootPath function requires:

  • SELECT permission on the FileTable to get the root path of a specific FileTable.

  • db_datareader or higher permission to get the root path for the current database.

Examples

The following examples show how to call the FileTableRootPath function.

USE MyDocumentDB;
-- returns “\\MYSERVER\MSSQLSERVER\MyDocumentDB”
SELECT FileTableRootPath();

-- returns “\\MYSERVER\MSSQLSERVER\MyDocumentDB\MyFileTable”
SELECT FileTableRootPath(N'dbo.MyFileTable');

-- returns “\\MYSERVER\MSSQLSERVER\MyDocumentDB\MyFileTable”
SELECT FileTableRootPath(N'MyFileTable');

See Also

Concepts

Work with Directories and Paths in FileTables