How to detemine content type of binary data in the image field of SQL Server 2008?

Question

I need to determine file type (i.e., MimeType) of stored data in the SQL Server 2008.

Is there anyway, if possible using SQL query, to identify the content type or MimeType of the binary data stored in the image column?

Answer 1

I think that, if you need that information, it would probably be better to store it in a separate column. Once it's in the DB, your only options really are guessing it from the file name (if you happen to store that) or by detecting the signature from the first few bytes of data.

Answer 2

There is no direct way in SQL Server to do that - there's no metadata on binary columns stored inside SQL Server, unless you've done it yourself.

For SQL Server, a blob is a blob is a blob - it's just a bunch of bytes, and SQL Server knows nothing about it, really. You need to have that information available from other sources, e.g. by storing a file name, file extension, mime type or something else in a separate column.

Marc