Query to find a value in all tables

[Former Member]

Hi,

Can you please let me know how to search for a value in all the tables in entire database.

Example: I want to find the id(say 101) in all the tables in the database.Can you pls help with a generic query in sybase.

Thanks in advance

[Former Member]

I'm guessing at some details of the problem you need to solve: are you looking only at a single column name for each table (as Breck assumed) or any column of any table (as Volker thought)?

Here's one thing you might want to consider if these types of queries are issued frequently and need to run quickly: you can use text indexes and the CONTAINS clause to find a row of a table where any column matches a string. You can then create a procedure that searches all tables in the database using the text index. You could either generate that table by hand or you could generate it using a query over SYSTAB. The created procedure could also be used with Breck's solution if you plan to run the query more than once

If this is a one-off where you need to look in all columns, the quickest way would be as Volker suggests -- unload to .dat files and use grep.

[Breck_Carter]

To build on Ivan's comment: An article about loading and full-text searching all of Wikipedia may be found here, and a Techwave presentation on the same topic may be found here... both are from long long ago, which makes the recent "discovery" of Wikipedia-as-demo-data by the HANA folks rather amusing, especially the comment about how it "shows how well HANA deals with data that is too large for other systems"...

...or to be blunter, "Hey, HANA, SQL Anywhere was doing that five years ago!" [snork]

[Breck_Carter]

This code runs OK in the SQL Anywhere 16 demo database:

BEGIN
DECLARE @select_statements LONG VARCHAR;
SELECT LIST ( STRING ( 'SELECT ''"', USER_NAME ( SYSTAB.creator ), '"."', SYSTAB.table_name, '"'' AS table_name, ',
                       '* FROM "', USER_NAME ( SYSTAB.creator ), '"."', SYSTAB.table_name,  
                       '" WHERE id = 101;\\X0D\
' ),
              '' ORDER BY USER_NAME ( SYSTAB.creator ), SYSTAB.table_name ) 
  INTO @select_statements
  FROM SYSTAB INNER JOIN SYSTABCOL
          ON SYSTAB.table_id = SYSTABCOL.table_id
 WHERE SYSTAB.table_type = 1 -- base table
   AND USER_NAME ( SYSTAB.creator ) IN ( 'GROUPO', 'DBA' )
   AND SYSTABCOL.column_name = 'id';
EXECUTE IMMEDIATE WITH RESULT SET ON @select_statements;
END;

Here's what it looks like in ISQL...

Here's what the string @select_statements contains:

SELECT '"GROUPO"."Contacts"' AS table_name, * FROM "GROUPO"."Contacts" WHERE id = 101;
SELECT '"GROUPO"."Customers"' AS table_name, * FROM "GROUPO"."Customers" WHERE id = 101;
SELECT '"GROUPO"."MarketingInformation"' AS table_name, * FROM "GROUPO"."MarketingInformation" WHERE id = 101;
SELECT '"GROUPO"."Products"' AS table_name, * FROM "GROUPO"."Products" WHERE id = 101;
SELECT '"GROUPO"."SalesOrderItems"' AS table_name, * FROM "GROUPO"."SalesOrderItems" WHERE id = 101;
SELECT '"GROUPO"."SalesOrders"' AS table_name, * FROM "GROUPO"."SalesOrders" WHERE id = 101;
SELECT '"GROUPO"."SpatialContacts"' AS table_name, * FROM "GROUPO"."SpatialContacts" WHERE id = 101;

[VolkerBarth]

Hm, I had the impression the OP wants to find all occurrences of "101" in any table and column - so I was about to "do a full unload and search within the .dat files... - not a real answer:(