CODEWITHSUNDEEP
oracle-databaseoracle11g
MakDeveloper
MakDeveloper

Reputation: 31

get an column name from the table by passing value in oracle

I want a query in oracle to get the column name from the table by passing value. Means that In most of the case - We write the query like that - select * from table where column = 'value'. But in my case i don't know the column name.

Can any one suggest me. Thanks in advance...

Upvotes: 0

Views: 2158

Answers (2)

Aleksej
Aleksej

Reputation: 22979

You can try to build a dynamic query to check all the tables of your DB.

setup:

create table tab1 ( v1 varchar2(100), n1 number, v1b varchar2(100));
create table tab2 ( v2 varchar2(100), n2 number, v2b varchar2(100));
create table tab3 ( v3 varchar2(100), n3 number, v3b varchar2(100));
insert into tab1 values ('Maria', 1, 'aa');
insert into tab1 values ('xx', 2, 'bb');
insert into tab2 values ('yy', 3, 'Maria');
insert into tab2 values ('zz', 3, 'cc');
insert into tab3 values ('WW', 4, 'DD');

build the dynamic query:

select 'select table_name,
               matches from (' || listagg(statement, ' UNION ALL ') within group (order by table_name) || ')
        where matches > 0'
from (     
    select 'select ''' || table_name ||
             ''' as TABLE_NAME, count(1) as MATCHES from ' || table_name || ' WHERE ' ||
             listagg(column_name || ' = ''Maria''', ' OR ') within group (order by column_name) as statement,
            table_name
        from user_tab_columns col
        where data_type = 'VARCHAR2'
        group by table_name
     )

This will return a query, that you can run to check all the tables; in my example, this will build the query (not formatted) :

SELECT table_name, matches
  FROM (SELECT 'TAB1' AS TABLE_NAME, COUNT(1) AS MATCHES
          FROM TAB1
         WHERE    V1 = 'Maria'
               OR V1B = 'Maria'
        UNION ALL
        SELECT 'TAB2' AS TABLE_NAME, COUNT(1) AS MATCHES
          FROM TAB2
         WHERE    V2 = 'Maria'
               OR V2B = 'Maria'
        UNION ALL
        SELECT 'TAB3' AS TABLE_NAME, COUNT(1) AS MATCHES
          FROM TAB3
         WHERE    V3 = 'Maria'
               OR V3B = 'Maria')
 WHERE matches > 0;

Running this query will give:

TABL    MATCHES
---- ----------
TAB1          1
TAB2          1

Please notice that I used USER_TAB_COLUMNS, thus searching only in the tables of the login schema; if you want to search in different schemas, you can use ALL_TAB_COLUMNS or DBA_TAB_COLUMNS, depending on what you need and on the privileges of you user; see here for something more.

Also, consider that USER_TAB_COLUMNS will get the colums of tables and views; if you want to limit your search to tables, you can join USER_TAB_COLUMNS(ALL_TAB_COLUMNS, DBA_TAB_COLUMNS) to USER_TABLES (ALL_TABLES, DBA_TABLES) by TABLE_NAME, or TABLE_NAME and OWNER If you decide to use ALL or DBA tables:

SQL> create view vTab1 as select * from tab1;

View created.

SQL> select count(1)
  2  from user_tab_columns
  3  where table_name = 'VTAB1';

  COUNT(1)
----------
         3

SQL> select count(1)
  2  from user_tab_columns
  3       inner join user_tables using(table_name)
  4  where table_name = 'VTAB1';

  COUNT(1)
----------
         0

SQL>

Upvotes: 0

Chintan Gor
Chintan Gor

Reputation: 1072

select table_name from user_Tables where table_name = 'bogus';

Upvotes: -2

Related Questions