searching for a table field

[FALCONSEYE]

Another 'how do i do this in oracle' question .

sql version >

SELECT dbo.sysobjects.name
FROM dbo.syscolumns INNER JOIN
dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.xtype  = 'U' 
and dbo.syscolumns.name = 'description'

this will give me all the db tables (whatever schema i am in) that has a column named "description"

how do i do this in oracle?
I found this :

select * from all_tab_columns
where column_name like '%description%';

but it returns 0 rows. I know for a fact that my product table does have a description field in that schema.

thanks in advance.

http://www.fuzzysiberians.com

 

[FALCONSEYE]

keep forgetting that oracle is case sensitive.

select * from all_tab_columns
where column_name like '%DESCRIPTION%'

works fine.

Next question, what if I want to search stored procs, views, and functions for this column. Is it possible?

http://www.fuzzysiberians.com

 

[FALCONSEYE]

ha. find that one too:

select * from all_source
where upper(text) like '%DESCRIPTION%'

gives the owner, type (package/fnc), and line number.

http://www.fuzzysiberians.com

 

[SantaMufasa]

keep forgetting that oracle is case sensitive.

Actually, Falcon, Oracle is not case sensitive:

SQL> select * from dual;

D
-
X

SQL> select * from DUAL;

D
-
X

SQL> SeLeCt * FrOm DuAl;

D
-
X

Oracle does, however, store all of its objects names, by default, in UPPERCASE, regardless of the case you use to create the object. If you want to create an object with a case-sensitive name, then you must do so using double quotes in all references to the name:

SQL> create table "falcon" (x number);

Table created.

SQL> select * from falcon;
select * from falcon
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from "falcon";

no rows selected

Following this technique results in extreme derision from Oracle professionals...it is bad form in Oracle because it creates nothing but migraine headaches when writing code or referencing objects...Do Not Use Double Quotes to Create Objects or Columns in Oracle !!!


Does that clarify things a bit? If you have additional questions, please post.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[FALCONSEYE]

I was talking about

select * from all_source
where upper(text) like '%DESCRIPTION%'

vs.

select * from all_source
where text like '%description%'

the second one returns 0 records.
It will save me some time if i don't keep forgetting this.

http://www.fuzzysiberians.com

 

[Turkbear]

Hi,
Its all about knowing your data ( or Oracle's data) -
The LIKE operator ( and the = ), when given a literal, will look for that exact value.








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[FALCONSEYE]

d'oh. sql 101.

http://www.fuzzysiberians.com