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 (http://blog.sqlauthority.com)

5 thoughts on “SQL SERVER – Working with FileTables in SQL Server 2012 – Part 3 – Retrieving Various FileTable Properties

  1. Pinal,

    Always enjoy reading your blog.

    Do you know if it is possible to backup/restore just the non-filestream file groups on a database with only FILETABLEs? We are thinking of backup/restore of our large (>2mb) filestream files in FILETABLES via other processes and letting SQL take care of the FILETABLE non-filestream data. If this backup/recovery scenario is possible, we hope to achieve significant performance gains by only backing up the non-filestream filegroups via SQL and using SAN replication on the filestream filegroups.

    John

    Like

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s