find the tables has binary data

Question

How to find what are all tables has binary data and display the table name ?

Answer 1

That depends a lot on what database you are using. Many databases (at least MySQL and PostgreSQL, maybe all) has a database called information_schema (or something similar). This is a database describing the structure of your databases along with all tables, their fields and what types of data the fields hold. So this would be a good starting point.

Answer 2

Can you ask the DBA or the DB-developper?

if no, what DB (Oracle, MySql, Microsoft, other?) are you using ..

EDITED for MySQL DB

Use

select table_schema
     , table_name
     , column_name
     , data_type 
from information_schema 
where data_type like '%blob%' 
   or data_type in ('binary','varbinary')

Answer 3

I'd need to know which database, but this (or something very similar) should work on Oracle:

select *
  from all_tab_columns
 where data_type in ('BLOB', 'RAW')
;

Answer 4

you need to select the system tables (in msssql 2000 - syscolumns) or system management views (in mssql 2005 or 2008 - sys.columns) to find the columns with system_type_id you need to find, and then find the corresponding table joining sys.columns and sys.objects by object_id field.

Answer 5

For MySql you want the INFORMATION_SCHEMA COLUMNS table:

http://dev.mysql.com/doc/refman/5.1/en/columns-table.html

If you need to find all of the tables which have binary columns, then you can create a query joining with the INFORMATION_SCHEMA TABLES table:

http://dev.mysql.com/doc/refman/5.1/en/tables-table.html