1

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.

1
  • I'm not quite sure what you are asking. The pathname function will tell you where the blob is physically stored link HTH
    – la_mochila
    Feb 26, 2020 at 11:07

0

Your Answer

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

Browse other questions tagged or ask your own question.