One of the questions that might come to your mind while getting started with FILESTREAM is the way FILESTREAM directories and files are named. Within the FILESTREAM Data Container, SQL Server creates a root folder per FILESTREAM enabled table. If the table is partitioned, a folder will be created for every partition. Within the root folder of the table (or partition), a sub folder will be created for every FILESTREAM column in the table.
You might find the way these folders named very confusing. FILESTREAM folders show up as GUID values such as “5c3769cb-b53a-4da3-a5a8-d5e8ff31fb45”. There was no known way of relating a folder name with a table, until Paul Randal came up with an interesting blog post which shows a query that can be used to relate the directories to their respective tables and columns. You can find Paul’s blog post here.
The query that Paul posted reads the information from some internal system tables which is not available when you connect to SQL Server normally. To access these internal tables, you need to connect to SQL Server using a special method known as Dedicated Administrator Connection (DAC). To establish a DAC to your SQL Server instance, you can do the following.
Tags: FILESTREAM, TSQL, SQLSERVER, BRH, DAC, #FILESTREAM, #TSQL,
Thank you for sharing!