I'm teaching the Microsoft Certified Masters – Database qualification this week here in Redmond, and in part of day one I discuss the FILESTREAM directory structure. I was asked the question where do the directory name GUIDs come from? so I started digging around in the system tables while Kimberly was lecturing. Take a look at my previous blog post (FILESTREAM directory structure) from last week to see the database schema I'm working with. I recreated it again and wrote some queries to find where the GUIDs are stored, as they have to be stored in the database somewhere.

Here's the query to find all the FILESTREAM directory names, for both levels of directory (and you have to run this through the DAC as it's accessing undocumented, hidden system tables):

SELECT o.name AS [Table],
    cp.name AS [Column],
    p.partition_number AS [Partition],
    r.rsguid AS [Rowset GUID],
    rs.colguid AS [Column GUID]
FROM sys.sysrowsets r
    CROSS APPLY sys.sysrscols rs
    JOIN sys.partitions p ON rs.rsid = p.partition_id
    JOIN sys.objects o ON o.object_id = p.object_id
    JOIN sys.syscolpars cp ON cp.colid = rs.rscolid
WHERE rs.colguid IS NOT NULL AND o.object_id = cp.id
AND r.rsguid IS NOT NULL AND r.rowsetid = rs.rsid;
GO

See below for a screenshot of this using my scenario.

You can see that I'm connected through the admin connection in SSMS and that the top-level directory name is derived from the rowset GUID, with the column-level directory name is derived from the column GUID. Note that some parts are byte-reversed, but they're definitely the right GUIDs.

Enjoy!