0

I know the database and table name and need to find a column name. Example as in emp table; I know data 7369 and table name as emp, and I need to get the column name as empno. My table has hundreds of columns and it is getting difficult to search each column name.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
user2295687
  • 1
  • 2
  • Can you tell us what you're trying to accomplish? This might help someone help you. – GHC Apr 18 '13 at 15:39
  • 2
    You could adapt one of the answers from [here](http://stackoverflow.com/q/208493/266304) or [here](http://stackoverflow.com/q/6389666/266304) (or many others you could search for) to only look at a specific table. Also, by 'database' do you really mean 'schema'? – Alex Poole Apr 18 '13 at 16:37

2 Answers2

1

You don't have any choice but to search in every column. Please note though that this value could, potentially, appear in multiple columns and/or multiple times in a single column. There's no way to restrict how often it appears across an entire table.

This is the point of a database; everything stored in a column and, most importantly, that column has meaning. If you disassociate the data stored in a column from a meaning then you will have to search everything.

Ben
  • 51,770
  • 36
  • 127
  • 149
0

Two steps, not using cursors or complex pl/sql, only SQL Plus.

  1. Produce your search queries:

    select select '|| COLUMN_NAME || ',count(*) from emp where ' || column_name || ' = 7369 group by '|| COLUMN_NAME || ';'
    from cols where table_name = 'EMP';

EG:

--------------------------------------------------------------------------------------
select SECOND,count(*) from TESTER where SECOND = 7369 group by SECOND;

(in my env, Second was a column in table TESTER)

Capture the output, clean up the headers and the like, and run it.

It will return every column that matches, along with a count of how many rows matched.

marceljg
  • 997
  • 4
  • 14