image data-types 2

[cfw2]

I have a table that has approx 14,000 records in it. 2 of the fields are image fields. The table takes up more than 8 gigabytes, and the database has only been in production for 3 months. Data will be retained in the table for close to 5 years. I am trying to identify which records have the largest file attachments, and what the file types are.

Can someone please help?

thank you
charles

 

[NoCoolHandle]

Charles... the bad news is that I don't think you can do this... (I have tried to do len on a blob before, and it just wont let you...>)

I use blobs alot and tend to take the path of storeing all the files relevant information as I move it to the database.

FileDates, Modified Dates, FileSize, Original Name, Original Location... etc.

Then I can query the metadata that I have stored to answer such questions...

You might want to evaluate making changes to your schema to enable additional information.

With your existing data, write an app that opens a stream and then reads the stream length, storeing the size back to the db.. With only 8 gigs of data you should be able to do that reasonably quickly.

Rob

 

[JamesLean]

You can use the DATALENGTH function to find out the size of the image values.

But there's no way through native t-sql to get the file types.

--James

 

[cfw2]

It is a good idea to store the file information in the database along with the file to make finding the file-type and other important information easier. I am going to see what I can do about having that added.

The datalength function worked perfect to find the largest sized records.

Thank You both for your help!
Charles