Get Total Image / binary table size

Question

Get Total Image / binary table size

Ness

SSCarpal Tunnel

Points: 4282
More actions
August 10, 2010 at 7:31 am

#387542

Hi All,
I have a couple of binary / image columns in a table and I need to get the total size that they are occupying in the table. I know I can get a maximum guess from the datatype max size and multiplying by the no of rows but is there a way of determining the actual size for the table?
Thanks in advance
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

right click the table in SSMS and view properties. select General on the left, in the right panel under the storage section it will show you the data space and the index space consumed.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 2

Sorry - perhaps I did not expain myself properly - I need to get the value in Mb of the total data size i.e. total of actual file size of each image in each row, not the whole table size

Thanks.

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

You could do something like select sum(datalength([binarycolumnhere])).

That would get you a reasonable close estimate. It would not take into account index space usage but should be pretty close. Maybe somebody else has encountered this before.