How to select all files of a SQL Server database from blob folder?

Question

I have a SQL Server database.

You can use the following script to create similar database:

CREATE DATABASE [test]
CONTAINMENT = NONE
ON PRIMARY 
   (NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\test.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ), 
   FILEGROUP [FS] CONTAINS FILESTREAM  DEFAULT
   (NAME = N'FS', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\FS' , MAXSIZE = UNLIMITED)
   LOG ON 
   (NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\test_log.ldf', SIZE = 8192KB, MAXSIZE = 2048GB, FILEGROWTH = 65536KB )
GO

It's clear how to select all database files:

select * from sys.database_files

file_id     file_guid                            type type_desc                                                    data_space_id name                                                                                                                             physical_name                                                                                                                                                                                                                                                    state state_desc                                                   size        max_size    growth      is_media_read_only is_read_only is_sparse is_percent_growth is_name_reserved is_persistent_log_buffer create_lsn                              drop_lsn                                read_only_lsn                           read_write_lsn                          differential_base_lsn                   differential_base_guid               differential_base_time  redo_start_lsn                          redo_start_fork_guid                 redo_target_lsn                         redo_target_fork_guid                backup_lsn
----------- ------------------------------------ ---- ------------------------------------------------------------ ------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----- ------------------------------------------------------------ ----------- ----------- ----------- ------------------ ------------ --------- ----------------- ---------------- ------------------------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ------------------------------------ ----------------------- --------------------------------------- ------------------------------------ --------------------------------------- ------------------------------------ ---------------------------------------
1           07DE2F09-B9C3-4CC9-AC7F-C7BBEDBFE572 0    ROWS                                                         1             test                                                                                                                             C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\test.mdf                                                                                                                                                                                     0     ONLINE                                                       1024        -1          8192        0                  0            0         0                 0                0                        NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                 NULL                    NULL                                    NULL                                 NULL                                    NULL                                 NULL
2           25C00910-F2F0-48EF-8AE6-DB3E947DF0F3 1    LOG                                                          0             test_log                                                                                                                         C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\test_log.ldf                                                                                                                                                                                 0     ONLINE                                                       1024        268435456   8192        0                  0            0         0                 0                0                        NULL                                    NULL                                    NULL                                    NULL                                    NULL                                    NULL                                 NULL                    NULL                                    NULL                                 NULL                                    NULL                                 NULL
65537       DB6C446D-E1E1-4F5B-BC27-92168A94A92B 2    FILESTREAM                                                   2             FS                                                                                                                               C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\FS                                                                                                                                                                                           0     ONLINE                                                       0           -1          0           0                  0            0         0                 0                0                        36000000012900001                       NULL                                    NULL                                    NULL                                    NULL                                    NULL                                 NULL                    NULL                                    NULL                                 NULL                                    NULL                                 NULL

(3 rows affected)

Now let's create a table with blobs:

USE [test]

CREATE TABLE TestBlob
(
    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, 
    [tbBin] VARBINARY(MAX) FILESTREAM NULL
)
GO

And put there some data:

USE [test]

INSERT INTO TestBlob (Id, tbBin)
    SELECT NEWID(), BulkColumn FROM OPENROWSET(Bulk 'C:\Everyone\small', Single_Blob) AS tb 

INSERT INTO TestBlob (Id, tbBin)
    SELECT NEWID(), BulkColumn FROM OPENROWSET(Bulk 'C:\Everyone\another', Single_Blob) AS tb 

Now blob folder looks like this:

cmd> tree "C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\FS" /F

C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL14.SQLEXPRESS\MSSQL\DATA\FS
│   filestream.hdr
│
├───$FSLOG
└───f4bf1197-bb52-4246-95ba-4b7622065c19
    └───4cf08168-3853-4c3d-ada2-4a1ff8d19900
            00000024-000000b1-0002
            00000024-000000cd-0002

I need a T-SQL query to select all blob files of a database.

Here it's the following files (my database uses them):

00000024-000000b1-0002
00000024-000000cd-0002

Anyone could help me with that ?

I also know that it's possible to use undocumented stored procedure:

EXEC master.sys.xp_dirtree 'C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL14.SQLEXPRESS\MSSQL\DATA\FS',0,1;

subdirectory                                                                                                                                                                                                                                                     depth       file
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
$FSLOG                                                                                                                                                                                                                                                           1           0
f4bf1197-bb52-4246-95ba-4b7622065c19                                                                                                                                                                                                                             1           0
4cf08168-3853-4c3d-ada2-4a1ff8d19900                                                                                                                                                                                                                             2           0
00000024-000000b1-0002                                                                                                                                                                                                                                           3           1
00000024-000000cd-0002                                                                                                                                                                                                                                           3           1
filestream.hdr                                                                                                                                                                                                                                                   1           1

(6 rows affected)

But it works with file system. I mean it doesn't consider if a file is used by the database or not.