Home » SQL & PL/SQL » SQL & PL/SQL » search for a column value in all tables of a schema (Oracle 10 G)
search for a column value in all tables of a schema [message #420251] Thu, 27 August 2009 15:57 Go to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
Hi Experts,
I have 2 questions.
1. I need to search for a column value in all the tables of a schema.
2. search for a value in entire schema (including all the objects)

Example:
1. I need to search for a column value like "TELEMOBILE" in all the columns of all the tables.
2. I need to search for a value like "TELEMOBILE" in all the objects (including tables, procedures, packages, synonyms, etc...) of entire schema


Help would be more appreciated.

Thanks in advance

Murali
Re: search for a column value in all tables of a schema [message #420252 is a reply to message #420251] Thu, 27 August 2009 17:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Is it correct to say that the solution for #1 is a subset of solution for #2.

You'll need to have a unique implementation for each object type.
Start posting your solutions one object type at a time.

Re: search for a column value in all tables of a schema [message #420253 is a reply to message #420252] Thu, 27 August 2009 18:35 Go to previous messageGo to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
They are not subset of each other.
Both are unique questions. If you have answer for any one of them, that would be fine.
Re: search for a column value in all tables of a schema [message #420254 is a reply to message #420251] Thu, 27 August 2009 19:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>1. I need to search for a column value like "TELEMOBILE" in all the columns of all the tables.
>2. I need to search for a value like "TELEMOBILE" in all the objects (including tables,
>They are not subset of each other.
How/where could value "TELEMOBILE" reside in a table except as a column value?

> If you have answer for any one of them, that would be fine.
You have not stated what is desired/expected output when the desired string is found so I don't know how to formulate an answer.

use USER_TAB_COLUMNS to produce SQL to query schema tables.
Re: search for a column value in all tables of a schema [message #420256 is a reply to message #420254] Thu, 27 August 2009 21:07 Go to previous messageGo to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
My desire is, identify the table names and column names for which the column value is like "TELEMOBILE". what i mean by identifying is, first i should know those table and column names. based on the list of table and column names i will decide what actions i have to perform with them.
Re: search for a column value in all tables of a schema [message #420270 is a reply to message #420251] Fri, 28 August 2009 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1. Search in DBA_TAB_COLUMNS
2. Search in DBA_SOURCE, DBA_SYNONYMS...

Youj can also try to search for "find_string" function in AskTom site and adapt it for your case.

Regards
Michel
Re: search for a column value in all tables of a schema [message #420280 is a reply to message #420251] Fri, 28 August 2009 01:54 Go to previous messageGo to next message
sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai
Member
Hi,

Try this one

--For Table Name Search
select * from user_col_comments
where table_name = 'TELEMOBILE';
-- For Column Search
select * from user_col_comments
where column_name = 'TELEMOBILE';

Re: search for a column value in all tables of a schema [message #420476 is a reply to message #420270] Sun, 30 August 2009 09:55 Go to previous messageGo to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
Thanks for you reply.

Michel Cadot,
DBA_TAB_COLUMNS dictionary view will only display number of columns and its type which are the part of the table. It will not display the values of each column of each row. so, this will not solve my purpose. And the same thing is applicable for DBA_SOURCE, DBA_SYNONYMS dictionary views also.

But, i will "find_string" function in AskTom site.


sen_sam86,
user_col_comments will only display the comments of the column, but it will not display the values of each row of each column. so, this will not solve my purpose.

My intention is to find a string (in my example "TELEMOBILE") through out the database where ever it is used.

Hope you all understand my requirement now.
I appreciate for your responses.

Regards
Murali
Re: search for a column value in all tables of a schema [message #420477 is a reply to message #420251] Sun, 30 August 2009 10:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>My intention is to find a string (in my example "TELEMOBILE") through out the database where ever it is used.
It appears it is your intention to have others solve this assignment for you.

You need to follow Posting Guidelines and post what you have tried.
Re: search for a column value in all tables of a schema [message #420478 is a reply to message #420476] Sun, 30 August 2009 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
My intention is to find a string (in my example "TELEMOBILE") through out the database where ever it is used.

And "findstring" will do it.

Quote:
And the same thing is applicable for DBA_SOURCE

Wrong DBA_SOURCE contains the source (the code) of the procedure and so on, so it answers your question for this kind of objects.

Regards
Michel
Re: search for a column value in all tables of a schema [message #420513 is a reply to message #420251] Mon, 31 August 2009 01:23 Go to previous messageGo to next message
sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai
Member
For searching the values in the record set u just try this one,
hope this ll help u....?

SELECT * FROM user_source WHERE text = 'TELEMOBILE';
Re: search for a column value in all tables of a schema [message #420514 is a reply to message #420513] Mon, 31 August 2009 01:31 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Nice you want to help but please read OraFAQ Forum Guide and don't use IM/SMS speak.

Regards
Michel

[Updated on: Mon, 31 August 2009 01:31]

Report message to a moderator

Previous Topic: group data by 6 months
Next Topic: Time Duration Calculation (merged 3)
Goto Forum:
  


Current Time: Mon Feb 17 01:46:20 CST 2025