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

asked 19 Mar '14, 09:06

sb537's gravatar image

sb537
16224
accept rate: 0%


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.

permanent link

answered 19 Mar '14, 11:17

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
2.8k22732
accept rate: 39%

1

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]

(19 Mar '14, 14:39) 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\X0A' ),
              '' 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...

alt text

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;
permanent link

answered 19 Mar '14, 10:27

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852
accept rate: 20%

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:(

(19 Mar '14, 10:49) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×34

question asked: 19 Mar '14, 09:06

question was seen: 2,376 times

last updated: 19 Mar '14, 14:41