SQL SERVER – Working with FileTables in SQL Server 2012 – Part 3 – Retrieving Various FileTable Properties

Read Part 1 Working with FileTables in SQL Server 2012 – Part 1 – Setting Up Environment

Read Part 2 Working with FileTables in SQL Server 2012 – Part 2 – Methods to Insert Data Into Table

In this third part of the series, we will see how we can retrieve various information from the FileTable database.

Identify Directory or File

You can run following query which will give details if query is file or directory.

USE FileTableDB
GO
SELECT [name]
,[file_type]
,CAST([file_stream] AS VARCHAR) FileContent
,[cached_file_size]
,[is_directory]
FROM [dbo].[FileTableTb]
GO

If [is_directory] is 1 it means the row belongs to folder.

Root Level Path of the File

SELECT FileTableRootPath('FileTableTb') AS FileTableRootPath

Above query will return the filepath where the filetable has stored the files

\\MyPersonalServer\MSSQLSERVER\FileTableDB\FileTableTb_Dir

Specific Locator ID for a file by providing the path

SELECT GetPathLocator('\\MyPersonalServer\MSSQLSERVER\FileTableDB\FileTableTb_Dir')

Above query will provide the locator ID for the file.

Relative path of particular file or directory

SELECT file_stream.GetFileNamespacePath()
FROM [dbo].[FileTableTb]

Above query will return following resultset. It will give relative path from the FileTable folder with hierarchy of the directory.

\FileTableTb_Dir\mydir
\FileTableTb_Dir\mydir\SharePoint SSIS Adapters 2011.docx
\FileTableTb_Dir\5 Tips for a Smooth SSIS Upgrade to SQL Server 2012.docx

There are more to FileTable and we will see those in my future blog posts.

Reference: Pinal Dave (https://blog.sqlauthority.com)