Home » SQL & PL/SQL » SQL & PL/SQL » USER_TAB_COLUMNS and Other Schemas
USER_TAB_COLUMNS and Other Schemas [message #242445] Sat, 02 June 2007 03:15 Go to next message
murali_gvn
Messages: 2
Registered: May 2007
Junior Member
Hi Expert,

I used to use USER_TAB_COLUMNS to find the tables with a particular column name.

My Table Name is: tblName;
My Schema name is: mySchema;
My Column name is: colName;

My current Schema is: curr_Schema;

The query that I used is:

select count(*) from user_tab_columns where columnname = 'colName' and table_name='tblName';

The query returned the result as 0 though there is a column 'colName' in 'tblName' table of mySchema;

I expect I need to specify the schema name in my query.

Can anyone help me solve my problem?

Thanks,
Muralidhar.
Re: USER_TAB_COLUMNS and Other Schemas [message #242449 is a reply to message #242445] Sat, 02 June 2007 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
USER_% views give what you own, that is what's inside your schema.
ALL_% views give what you have access to.
DBA_% views give data for all users, but only if you have access to them.

Regards
Michel

[Updated on: Sat, 02 June 2007 03:32]

Report message to a moderator

Re: USER_TAB_COLUMNS and Other Schemas [message #242479 is a reply to message #242445] Sat, 02 June 2007 11:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
murali_gvn wrote on Sat, 02 June 2007 10:15

select count(*) from user_tab_columns where columnname = 'colName' and table_name='tblName';

The query returned the result as 0 though there is a column 'colName' in 'tblName' table of mySchema;


Since you seem to have obfuscated your query, I don't know if you also changed case of your object names.
Normally object names are in all upper case in Oracle.
Search for COLNAME and TBLNAME

Of course, this is in addition to what Michel said, you also have to check the right view.

[Updated on: Sat, 02 June 2007 11:40]

Report message to a moderator

Re: USER_TAB_COLUMNS and Other Schemas [message #242493 is a reply to message #242445] Sat, 02 June 2007 15:01 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
murali_gvn wrote on Sat, 02 June 2007 09:15
My Table Name is: tblName;
My Schema name is: mySchema;
My Column name is: colName;

My current Schema is: curr_Schema;

The query that I used is:

select count(*) from user_tab_columns where columnname = 'colName' and table_name='tblName';

The query returned the result as 0 though there is a column 'colName' in 'tblName' table of mySchema;


Is the table named 'tblName' or (more likely) 'TBLNAME'? See this OTN thread.

Are you sure your current schema is different to your schema? I'm not sure what you mean, unless you logged in as myschema then set the current schema explicitly with:

alter session set current_schema = curr_schema;

USER_TAB_COLUMNS reports columns for the logged-in user, not the current schema.
Previous Topic: Help with auto trace output.
Next Topic: PLSQL Collections
Goto Forum:
  


Current Time: Sun Dec 04 20:30:40 CST 2016

Total time taken to generate the page: 0.09292 seconds