Searching a database from datatype

Question

I've actually forgotten that in which table of my database I've stored my images as binary

I have 300 tables and I can't check manually. I even don't remember the column name of the image section.

Is there any query so that i can find my images and its corresponding table which contains that images..

Thanks for help in advance

Answer 1

You can try something like this (SQL Server 2005 and up):

SELECT 
    t.Name 'Type name',
    OBJECT_NAME(col.object_id) 'Table name',
    col.*
FROM 
    sys.columns col
INNER JOIN 
    sys.types t ON col.user_type_id = t.user_type_id
WHERE 
    t.name = 'varbinary'

but you need to at least remember what data type you've used!

Recommended would be VARBINARY(MAX), and you should definitely no longer use IMAGE.