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):

    [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];

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.