0

I've seen similar queries where a string or character value is used to search an entire database. Those queries do not return results that lie in a BINARY(8) field. I've tried to modify those queries to no avail.

Is there a way to search the entire database for specific binary values, such as 0x0000000000000017?

Thanks guys.

smedley89
  • 21
  • 2
  • http://stackoverflow.com/questions/16188257/querying-binary-column-using-like-in-sql-server is a similar question with a decent answer – zedfoxus Mar 26 '14 at 20:52
  • I don't understand what you are asking. Do you want to search the entire database (so every field of every table) or the binary(8) field? – Patrick Hofman Mar 26 '14 at 20:53
  • I would be happy to find just the binary(8) fields, but every table with that type in the database. The queries I've seen so far only search text. – smedley89 Mar 26 '14 at 21:00

2 Answers2

0

You can use the system tables to find this.

MSSQL:

 SELECT t.name AS table_name,
  c.name      AS column_name,
  ty.name
FROM sys.tables AS t
  INNER JOIN sys.columns c
    ON t.OBJECT_ID = c.OBJECT_ID
  INNER JOIN sys.types ty
    ON t.schema_id          = ty.schema_id
WHERE ty.system_type_id = 173

ORACLE:

SELECT owner,table_name, column_name,data_TYPE 
  FROM all_tab_columns where data_TYPE = 'RAW';
CodeMonkey
  • 1,087
  • 2
  • 15
  • 24
0

Well...

select *
from foo
where foo.binary8column  = 0x0000000000000017

should do. If you want to enumerate all the tables and find all the binary or varbinary columns, this query

select table_name  = object_schema_name(tn.object_id) + '.' + tn.name ,
       column_name = c.name ,
       type        = t.name + '(' + convert(varchar,c.max_length) + ')'
from sys.types   t
join sys.columns c  on c.system_type_id = t.system_type_id
join sys.tables  tn on tn.object_id     = c.object_id
where t.name in ( 'binary', 'varbinary' )
  and c.max_length >= 8

should give enough information to generate the queries for every such table.

Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135
  • Thank you, this does return all tables/columns that have the binary(8) type. I was hoping to be able to search for a specific value in all table/columns of that type. Say, search database_name for 0x000123asd, and have a return that tells me where that value occurs across the entire database. I'll keep playing with it, thanks for the head start! – smedley89 Mar 27 '14 at 13:35
  • Hi. Were you able to get this code to work for you? I am searching for the same thing. – Rick Apr 02 '20 at 13:44