Sql Server FILESTREAM Total File Size

Is there a que开发者_如何学Pythonry that would get me the total file size of the files that are in the FILESTREAM folder on the disk?

The following query will return the length in bytes of the filestreamcolumn column:

SELECT SUM(DATALENGTH(filestreamcolumn)) FROM filestreamtable;

Source

One disadvantage of Remus' solution is that it will not include the old versions of files that are available for garbage collection. They will no longer be part of logical database, but will still consume disk space until the Filestream garbage collector deletes them.

Starting with Sql Server 2008 R2, you can query the size column of sys.database_files for an approximate size (i.e. disk space used) of a given filestream container.

use <DATABASE_NAME_HERE>
declare @table as nvarchar(100);
declare @columnName as nvarchar(100);
declare @query as nvarchar(500);
declare @sizeValue as nvarchar(100);
declare @generalValue as decimal(38);
declare @generalValueInMb as decimal(15,2);
set @generalValue = 0;

DECLARE tableCursor CURSOR FOR
(
SELECT DB_NAME()+'.**<schemaname_here>**.' + OBJECT_NAME(object_id) AS [FullTableName], name As [ColumnName]
FROM sys.columns
WHERE is_filestream = 1 
)

Open tableCursor

fetch next from tableCursor into @table, @columnName

while @@FETCH_STATUS = 0
begin
set @query = 'select @sizeValue = (select sum(Datalength('+@columnName+')) FROM ' + @table +')';
exec sp_executesql @query, N'@sizeValue int output', @sizeValue output;

if @sizeValue is not NULL
begin
set @generalValue = @generalValue + @sizeValue
end

fetch next from tableCursor into @table, @columnName
end

close tableCursor
deallocate tableCursor

select @generalValueInMb = round(@generalValue / 1024 / 1024, 2)

select @generalValueInMb

Note that you need to type schema name in proper place in above code.

-- Improved version of AGR's answer with schema support

declare @table as nvarchar(100);
declare @columnName as nvarchar(100);
declare @query as nvarchar(500);
declare @sizeValue as nvarchar(100);
declare @generalValue as decimal(38);
declare @generalValueInMb as decimal(15,2);
set @generalValue = 0;

DECLARE tableCursor CURSOR FOR
(
SELECT DB_NAME()+'.'+ OBJECT_SCHEMA_NAME((object_id)) +'.'+ OBJECT_NAME(object_id) AS [FullTableName], name As [ColumnName]
FROM sys.columns
WHERE is_filestream = 1 
)

Open tableCursor

fetch next from tableCursor into @table, @columnName

while @@FETCH_STATUS = 0
begin
set @query = 'select @sizeValue = (select sum(Datalength(['+@columnName+'])) FROM ' + @table +' with (nolock))';
exec sp_executesql @query, N'@sizeValue int output', @sizeValue output;

if @sizeValue is not NULL
begin
set @generalValue = @generalValue + @sizeValue
end

fetch next from tableCursor into @table, @columnName
end

close tableCursor
deallocate tableCursor

select @generalValueInMb = round(@generalValue / 1024 / 1024, 2)

select @generalValueInMb