Home » SQL & PL/SQL » SQL & PL/SQL » Search the Table Name or Column Name by Column Values (Oracle 9i)
Search the Table Name or Column Name by Column Values [message #400170] Sun, 26 April 2009 21:44 Go to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
How do I search the Table Name or Column Name by Column Values that is In Employee Table there is Salary Column so if I give the condition as the value "24000", I should get the Table Name or Column Name as "Employees"(Table Name) or "Salary"(Column Name). Is that possible in Oracle SQL?
Re: Search the Table Name or Column Name by Column Values [message #400171 is a reply to message #400170] Sun, 26 April 2009 22:04 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Is that possible in Oracle SQL
Yes

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Re: Search the Table Name or Column Name by Column Values [message #400173 is a reply to message #400171] Sun, 26 April 2009 22:50 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
If Yes then please provide me the the query. Thanks
Re: Search the Table Name or Column Name by Column Values [message #400174 is a reply to message #400170] Sun, 26 April 2009 22:52 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.


Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Re: Search the Table Name or Column Name by Column Values [message #400176 is a reply to message #400170] Sun, 26 April 2009 23:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For me your question is ununderstandable.
Why if you give 24000 you should not return roads with Length of 24000 meters in Roads table?
Explain in details and with example (as BlackSwan said abd following the guidelines) what you want.

Regards
Michel
Re: Search the Table Name or Column Name by Column Values [message #400182 is a reply to message #400170] Mon, 27 April 2009 00:12 Go to previous messageGo to next message
danish_fsd@yahoo.com
Messages: 38
Registered: February 2008
Location: Pakistan
Member
Hi,

Try following. Hope it will help you.

DECLARE
     v_count NUMBER;
     v_stmt VARCHAR2(2000);
BEGIN
     FOR i IN (SELECT DISTINCT Table_Name FROM User_Tab_Columns) LOOP
         FOR j IN (SELECT Column_Name FROM User_Tab_Columns WHERE Table_Name = i.Table_Name) LOOP
             v_stmt := 'SELECT COUNT(*) FROM '||i.Table_Name||' WHERE '||j.column_name||' LIKE '||''''||'%2400%'||'''' ;
             EXECUTE IMMEDIATE v_stmt INTO v_count;
     
             IF NVL(v_count, 0) > 0 THEN
                DBMS_OUTPUT.PUT_LINE(i.Table_Name ||'   '||j.Column_Name);
             END IF;
     
         END LOOP;
     END LOOP;     
END;


Regards,

Danish
Re: Search the Table Name or Column Name by Column Values [message #400211 is a reply to message #400182] Mon, 27 April 2009 03:11 Go to previous messageGo to next message
danish_fsd@yahoo.com
Messages: 38
Registered: February 2008
Location: Pakistan
Member
Hi,

You can find this by following too with One Cursor Loop.

DECLARE
     v_count NUMBER;
     v_stmt VARCHAR2(2000);
BEGIN
     FOR i IN (SELECT DISTINCT Table_Name, Column_Name FROM User_Tab_Columns ORDER BY 1) LOOP         
         v_stmt := 'SELECT COUNT(*) FROM '||i.Table_Name||' WHERE '||i.Column_Name||' LIKE '||''''||'%24000%'||'''' ;
         EXECUTE IMMEDIATE v_stmt INTO v_count;
     
         IF NVL(v_count, 0) > 0 THEN
            DBMS_OUTPUT.PUT_LINE(i.Table_Name ||'   '||i.Column_Name);
         END IF;
     END LOOP; 
END; 


Regards.

Danish
Re: Search the Table Name or Column Name by Column Values [message #400214 is a reply to message #400211] Mon, 27 April 2009 03:16 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Here you people are trying to get the Table Name and Column Name by column name like 24000 but what I need is get the Table Name and Column Name by column values 24000.


select * from employees;

EMPLOYEE_ID   SALARY
------------ -------
   100         2500
   121        24000
   122        17000

My output should be like this "Table Name EMPLOYEES or "Column Name SALARY"
Re: Search the Table Name or Column Name by Column Values [message #400217 is a reply to message #400214] Mon, 27 April 2009 03:23 Go to previous messageGo to next message
danish_fsd@yahoo.com
Messages: 38
Registered: February 2008
Location: Pakistan
Member
Hi,

I think you didn't run the query. It already give the desired result.

Here '24000' is the column value not the column name.

Danish
Re: Search the Table Name or Column Name by Column Values [message #400224 is a reply to message #400214] Mon, 27 April 2009 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your client application?
What is the EXACT output do your want?
Don't you have EMPLOYEE_ID and SALARY in the output your posted?

Regards
Michel
Re: Search the Table Name or Column Name by Column Values [message #400323 is a reply to message #400217] Mon, 27 April 2009 10:14 Go to previous message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Thanks you very much Danish,I run the query and got the desired result.

Previous Topic: Query to Copy data from one table to nother
Next Topic: error in partition table
Goto Forum:
  


Current Time: Fri Dec 02 20:27:28 CST 2016

Total time taken to generate the page: 0.16943 seconds