?
Solved

find a needle in a haystack using sql lite

Posted on 2013-11-16
7
Medium Priority
?
418 Views
Last Modified: 2013-11-24
using sql lite want to find a needle in a haystack

many tables and many columns in those tables

I want to find 'bob'

Is there a query or query tool to do this search

I do not want to do a large task that involves copy paste.

I have done this task with one query in sql server
0
Comment
Question by:rgb192
  • 3
  • 2
  • 2
7 Comments
 
LVL 23

Expert Comment

by:David
ID: 39653965
Conceptually, if you were using all XML or SQLText (binary) columns, my guess is that it's doable.  With OLTP, B-tree traditional systems (unindexed varchar datatypes), that becomes an exercise in futility.  IMHO, your results may vary.  

You're in effect asking, for 1..n tables in 1..n schemas, each having 1..n columns, make a pass each time through all rows in a given schema.table.column, searching for "%X%".
Then there's the possibility that the desired tuple exists but in a schema.table to which you are denied access.
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 1332 total points
ID: 39654050
If this is a single database it should be doable.

I'm not familiar with SQL Lite but the general idea is that there is going to be some sort of central table that lists off the rest of the tables in the DB. And you can write to a temporary table.

So it would be some sort of create temp table:
create #temptable(
     TableName as varchar(100),
     ColumnName as varchar(100),
     ColUmnValue as varchar(500))

Open in new window


Then create some sort cursor/wrapper from the system table:
cursor = select TableName from SysTables

Open in new window


Then some sort of tracking that it goes through the columns looking for 'bob':
While I < TableNameColumn.Count
RecExists = select count(*) from Cursor.TableName where Column(I) like '%' + 'bob' + '%'
     if RecExists >0 then
          INSERT INTO #temptable select TableNameColumn, Column(I).Name, Column(I).Value  from Cursor.TableName where Column(I) like '%' + 'bob' + '%'
     end if
Next I

Open in new window


Then you present the  #temptable to the user. This is the concept behind how to do it.
0
 
LVL 23

Expert Comment

by:David
ID: 39655014
Jimkin, assuming that this query ran as a sys level user, what kind of resource consumption do you think this would take?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 1332 total points
ID: 39655120
I have a similar script for SQL Server DB's. Running it as the SA user on 40GB DB with about 150 tables average of 30K rows it took about 4-5 minutes.

I wouldn't want to run it in major hours for a large DB online. But it shouldn't be too horrible.
0
 

Author Comment

by:rgb192
ID: 39656153
http://www.experts-exchange.com/Database/Miscellaneous/Q_28295935.html#a39654050

I do not understand syntax of step2:
cursor = select TableName from SysTables


I do not understand the other comments.
More data:
I am looking at main.db which skype uses.
I have used sql lite to view/modify tables.
Could I use another database?
0
 
LVL 23

Accepted Solution

by:
David earned 668 total points
ID: 39656420
The pseudo-syntax says, take a one-dimensional temporary array in memory, and populate it with n number of table names from a schema.  Then, loop through the array: for each table name, perform some dependent transaction (query).

It's my opinion that jimpen may be simplifying his/her process for your clarity.  I observe that the logic does not handle multiple schemas (accounts that own database objects).  In one of my systems, the number of schemas is 3-4 thousand,  The SYS schema owns over fifty thousand tables.

My point is, if you're looking for 'bob' in one or two schemas, the search is indeed doable, beyond that it is not scalable.
0
 

Author Closing Comment

by:rgb192
ID: 39673566
Thank you for a blueprint of what to do in sql lite.  I will use this information.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question