Home » SQL & PL/SQL » SQL & PL/SQL » to get table's column from user_objects
to get table's column from user_objects [message #210877] Fri, 22 December 2006 20:51 Go to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
hello everyone


Suppose there are lots of table, one of the table contain a column named email_id.So is there any query to get those table(which contains email_id column) from the tables? I dnt want to get it through describe.Becoz there,s no of tables contains no of columns, its not easy to check each every one.
Please provide me some help.

thanxx


regards

yas
Re: to get table's column from user_objects [message #210880 is a reply to message #210877] Fri, 22 December 2006 21:21 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
hello,
use USER_TAB_COLUMNS for tables in your own schema, DBA_TAB_COLUMNS for all tables in the database (should be connected as system or sys user or have a privilege to select from the table).
Keep in mind that the table and column names are in uppercase.
Re: to get table's column from user_objects [message #210909 is a reply to message #210880] Sat, 23 December 2006 03:19 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I partially don't agree; table name and/or column names may be in mixed case, if developer decided to enclose their names in double quotes.
SQL> CREATE TABLE brisime
  2  (uppercase_col    NUMBER,
  3   "lowercase_col"  NUMBER,
  4   "MIXED case col" NUMBER);

Table created.

SQL>
SQL>
SQL> SELECT column_name FROM user_tab_columns
  2  WHERE table_name = 'BRISIME';

COLUMN_NAME
------------------------------
UPPERCASE_COL
lowercase_col
MIXED case col

SQL>
Re: to get table's column from user_objects [message #210942 is a reply to message #210909] Sat, 23 December 2006 23:11 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi,

But OP's question is entirely different, he want to search the column email id whereever it comes 'in any table'.

How to make it possible?

Bye
Ashu
Re: to get table's column from user_objects [message #210958 is a reply to message #210942] Sun, 24 December 2006 04:49 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just like Flyboy said:
SQL> select table_name from user_tab_columns where column_name = 'DEPTNO';

TABLE_NAME
------------------------------
DEPT
EMP

SQL>

But, if someone created a table where DEPTNO column would be in lower (or mixed) case, this wouldn't work. Option would then be to select

... WHERE UPPER(column_name) = 'DEPTNO'
SQL> create table brisime ("DeptNo" number);

Table created.

SQL> select table_name from user_tab_columns where column_name = 'DEPTNO';

TABLE_NAME
------------------------------
DEPT
EMP

SQL> select table_name from user_tab_columns
  2    where upper(column_name) = 'DEPTNO';

TABLE_NAME
------------------------------
BRISIME
EMP
DEPT

SQL>
Previous Topic: Try this code
Next Topic: join condition update
Goto Forum:
  


Current Time: Wed Dec 07 14:32:13 CST 2016

Total time taken to generate the page: 0.07747 seconds