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.
3 thoughts on “FILESTREAM directory structure – where do the GUIDs come from?”
You could do some CASTing and CONVERT in your query to present the BINARY(16) value into CHAR(36) value for more readability.
DECLARE @guid BINARY(16)
SET @guid = 0x6DBC7BFD0211DF41A709A924FFE14CE4
SELECT @guid AS [BINARY(16)],
CAST(@guid AS UNIQUEIDENTIFIER) AS [UNIQUEIDENTIFIER],
CONVERT(CHAR(36), CAST(@guid AS UNIQUEIDENTIFIER)) AS [CHAR(36)]
Absolutely – there’s always all sorts of things to do to make the output better but I didn’t want to obfuscate the system table accesses with a bunch of ancilliary code, and it wouldn’t have fit in the screenshot. But thanks!
thanks for this, here is the version using system views :
o.[name] AS [Table],
c.[name] AS [Column],
p.[partition_number] AS [Partition],
p.filestream_guid AS [Rowset GUID],
pc.partition_column_guid AS [Column GUID]
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
JOIN sys.system_internals_partitions p ON o.object_id = p.object_id
JOIN sys.system_internals_partition_columns pc ON c.column_id = pc.partition_column_id
WHERE p.filestream_guid IS NOT NULL
AND pc.partition_column_guid IS NOT NULL