CODEWITHSUNDEEP
oracle-databaseoracle11g
user2295687
user2295687

Reputation: 1

Need Column name where I know the database and table names

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.

Upvotes: 0

Views: 111

Answers (2)

marceljg
marceljg

Reputation: 1067

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.

Upvotes: 0

Ben
Ben

Reputation: 52853

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.

Upvotes: 1

Related Questions