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.

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

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)

, ,
Previous Post
SQL SERVER – Performance Tuning – Part 2 of 2 – Analysis, Detection, Tuning and Optimizing
Next Post
SQLAuthority News – Fast Track Data Warehouse Reference Guide for SQL Server 2012

Related Posts

4 Comments. Leave new

  • hi pinal,

    Thaks its very nice one last saturday we will start learning same topic

    Reply
  • thank you pinal for the information

    Reply
  • 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

    Reply
  • Sifiso W. Ndlovu
    September 10, 2012 4:16 pm

    great articles on SQL Server File Tables, thanks.

    Reply

Leave a Reply

Menu