search a data in database

10 pts.
Tags:
Database query
SQL development
I want to find the data in a table,but i dont know the table name and column name.All I know is the data and the database name .

[strong]Is there a query to find the table and column which contains that data[/strong]



Software/Hardware used:
sql developer

Answer Wiki

Thanks. We'll let you know when a new response is added.

WOW – you want to search every column in every table looking for some piece of data?

Yes, you can build some queries to do that. I have done it on a limited set of tables during one of the conversions I was involved in. (there were six characters that had to be found and dealt with.)

Are you sure you want to do this? If you have lots of data, this could run for a LONG time.

first you will have to get a list of all tables from your RDBMS catalog
Can you limit or restrict yourself to only certain tables?

then a list of all columns in each of those tables.
can you limit the columns? to only numeric? or not dates?

then a SELECT statement for each of the columns in each table with a WHERE clause using a LIKE with wildcards at the front and the end of the string you are searching for
OR the WHERE could use a LOCATE function if your RDBMS has one.

I would suggest using SQL to create the SQL you will actually use to search for your data. It will save lots of typing.

You might INSERT into a results table when you find what you are looking for.

this is a sample of what you might want to end up with – this is for only one column in one table. this would be repeated for each and every column in every table that you need to search.

<pre>
INSERT INTO results_table
SELECT
‘table_owner_or_schema . table_name . column_name’
, COUNT(*)
FROM schema . table_name
WHERE LOCATE (‘what you are looking for’,column_name) <> 0
</pre>

I looked but I cannot locate the code I used back when I had to do this. I never throw anything away so it is here someplace. If I can find it, I will return to post it here.

good luck. this is not trivial. I again I caution on the run time this might take.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Linu4389, you asked the same question yesterday. Please continue the discussion in one of these instead of creating a new one to add more information/details or ask for clarification. Thanks,
    70,220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following