| USER_TAB_COLUMNS and Other Schemas [message #242445] |
Sat, 02 June 2007 03:15  |
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 #242479 is a reply to message #242445] |
Sat, 02 June 2007 11:39  |
Frank
Messages: 7901
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 |
William Robertson
Messages: 1643
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
