2

Every BLOB stored in a filestream-Enabled table actually stored in a physical file, How can I know the actual filename for a specific row in that table.

For example, I have the Images table which is filestream-enabled, it have a filestream column called Data its type : VARBINARY(MAX).

Here is an example of that table that have 19 rows and also the filestream folder contains 19 rows : an example of that table that have 19 rows and also the folder contains 19 rows

Is it stored in some system table, or there is direct relationship between the "ROWGUIDCOL" and its name ?

2
  • 1
    You can use the column_name.PathName - From BOL :Returns the path of a FILESTREAM binary large object (BLOB). The OpenSqlFilestream API uses this path to return a handle that an application can use to work with the BLOB data by using Win32 APIs. PathName is read-only.
    – Kin Shah
    Commented Dec 23, 2013 at 1:03
  • @Kin actually the value I get from PathName will allow me to access the file for example using SqlFileStream, but it'll not return the physical file name I need
    – Amer Sawan
    Commented Dec 29, 2013 at 0:54

1 Answer 1

2

Just as caveat it is best not to disturb the physical files involved with FILESTREAM for a database.

Below are a few good links on the architecture:
Paul Randal White Paper - FILESTREAM Storage
Paul Randal Blog Post - FILESTREAM directory structure

As quoted from Paul's blog post above:

The FILESTREAM file names are actually the log-sequence number from the database transaction log at the time the files were created. You can correlate these by looking at the data with DBCC PAGE, but first finding the allocated pages using sp_AllocationMetadata (see this blog post).

Note the stored procedure he mentions is an undocumented one that he provides some additional information to in the blog post he linked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.