Tried sp_spaceused - not there. Datalength - gives rezuttat, but you need to somehow link it with the fields ...

    1 answer 1

    For a specific table, you can create a query of the form

     select * from ( select 'TableName' as TableName, cast(sum(datalength([BlobColumn1])) as bigint) as [BlobColumn1], cast(sum(datalength([BlobColumn2])) as bigint) as [BlobColumn2] from [TableName] ) t unpivot (Size for [ColumnName] in ([BlobColumn1], [BlobColumn2])) u

    For all tables, the same can be done with a dynamic query.

    On sys.columns select LOB columns (a sign of this is max_length = -1 ). Binary-LOB can be filtered by attaching sys.types (with the condition name = 'varbinary' ) to sys.columns :

     declare @dynamicSql nvarchar(max); with t as ( select t.object_id, a.TableName from sys.schemas s join sys.tables t on t.schema_id = s.schema_id cross apply (select quotename(s.name) + '.' + quotename(t.name)) a(TableName) ), c as ( select c.object_id, c.name from sys.columns c join sys.types ct on ct.system_type_id = c.system_type_id and ct.name = 'varbinary' where c.max_length = -1 ) select @dynamicSql = stuff( (select 'union all select * from (select ''' + t.TableName + ''' as TableName, ' + c.ColList + ' from ' + t.TableName + ')t unpivot (Size for [ColumnName] in (' + c2.ColList + ')) u ' from t cross apply ( select stuff(( select ', cast(sum(datalength(' + quotename(c.name) + ')) as bigint) as ' + quotename(c.name) from c where c.object_id = t.object_id order by c.name for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') ) c(ColList) cross apply ( select stuff(( select ', ' + quotename(c.name) from c where c.object_id = t.object_id order by c.name for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') ) c2(ColList) where c.ColList is not NULL order by t.TableName for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, len('union all') + 1, ''); exec sp_executesql @dynamicSql;

    If you need data on columns of all LOB types (including nvarchar(max) and xml ), then you need to remove the sys.types query.

    • Looked at your request, twisted it, rewrote a little easier select @dynamicSql= (select stuff((select ' union select '''+t.name+''' id,sum(' + stuff((select '+isnull(datalength(['+c.name+']),0)' from sys.columns c where system_type_id in (select system_type_id from sys.types where name like '%var%') and c.object_id=t.object_id for xml path('')),1,1,'' )+ ') size from ' + t.name from sys.tables t for xml path('')),1,6,'')) + ' order by 1' , but thanks for the idea. - nick_n_a
    • @nick_n_a, without the с.max_length = -1 filter, your request will capture not only lob s, but also columns like varchar(20) . - i-one
    • Tell me plz, mssql varchar is not considered a blob? Is it stored in a data section, or in a separate stream as a blob (almost all DBMSs assume that varchar is a blob)? That varchar I considered blob. Your request gave me few fields, and not one varchar. - nick_n_a
    • one
      @nick_n_a, LOB-types include varbinary (max), varchar (max), nvarchar (max), xml , i.e. types that can have (almost) unlimited length. BLOB is Binary -LOB, i.e., eg varbinary (max), xml . The types varchar (max), nvarchar (max) are sometimes called CLOB (Character-LOB), but more often they are simply LOB. Types varchar, nvarchar, varbinary , in which the length is not max is usually not attributed to the LOB. However, sometimes they are called SLOB ( Short- LOB) because of the storage method, they are stored in IN_ROW_DATA, if there is a place for them, but if not, then in ROW_OVERFLOW_DATA (which is structured like LOB_DATA). - i-one