Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

Get File Size of "Image" data type in SQL Server

I have a column that is of "Image" data type. I store my pdf documents in this column. I would like to run a query to determine the total fize size in (mb) of my documents. How do I go about doing this?

Thanks,
0
tinman1412
Asked:
tinman1412
  • 2
  • 2
1 Solution
 
imran_fastCommented:
I think that should be done at the time of inserting the data.
Now you can do it use textcopy command to extract the file from image column into physical file and check the size..

If the direction is IN (/I) then the data from the specified 'file' is
copied into SQL Server, replacing the existing text or image value. If the
direction is OUT (/O) then the text or image value is copied from
SQL Server into the specified 'file', replacing any existing file.

TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
  [/D [database]] [/T table] [/C column] [/W"where clause"]
  [/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

  /S sqlserver       The SQL Server to connect to. If 'sqlserver' is not
                     specified, the local SQL Server is used.
  /U login           The login to connect with. If 'login' is not specified,
                     a trusted connection will be used.
  /P password        The password for 'login'. If 'password' is not
                     specified, a NULL password will be used.
  /D database        The database that contains the table with the text or
                     image data. If 'database' is not specified, the default
                     database of 'login' is used.
  /T table           The table that contains the text or image value.
  /C column          The text or image column of 'table'.
  /W "where clause"  A complete where clause (including the WHERE keyword)
                     that specifies a single row of 'table'.
  /F file            The file name.
  /I                 Copy text or image value into SQL Server from 'file'.
  /O                 Copy text or image value out of SQL Server into 'file'.
  /K chunksize       Size of the data transfer buffer in bytes. Minimum
                     value is 1024 bytes, default value is 4096 bytes.
  /Z                 Display debug information while running.
  /?                 Display this usage information and exit.

You will be prompted for any required options you did not specify.
0
 
doobdaveCommented:
Hi,
You can use the DATALENGTH() function to get the number of bytes used by the contents of a text/binary field (or any field for that matter).

Eg:
SELECT DATALENGTH(imagefield) FROM tablename WHERE pkcol = somevalue

That will give you the number of BYTES used.

If you want to get a total for all rows in the table then:
SELECT SUM(DATALENGTH(imagefield)) FROM tablename

Best Regards,

David
0
 
imran_fastCommented:
Hi David,

I check it and i extract one of the image from database the size of the physical file was
121498 = 118 kb  and when i check with datalength i get 233249      = 227 kb i don't know why this difference perhaps you have an idea

Regards

Imran

0
 
doobdaveCommented:
I'm not too sure, but here's my theory:

The DATALENGTH returns the number of BYTES used to store the data within the database... this is therefore how much space it uses to store the data.
Perhaps when you extract the contents oof the column and make it into an image again (or a pdf, or whatever), the actual binary data is converted in some way (which must indeed happen) and depending on the type of object it can take up more or less physical disk space.

Therefore, tinman1412, it all depends on whether you need to know the size of the object in physical form, as it were, or the amount of space required to store it in SQL server.
If it is the latter, then the method I suggested will work;

However, imran, I've just done some tests on the Northwind database, and the sizes seem to match so I'm not sure what's going on there.

Best Regards,

David
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now