|
|
|
|
|
|
|
|
|
|
| Re: How to find tables which does not have specific columns [message #574550 is a reply to message #574537] |
Sat, 12 January 2013 08:02   |
Solomon Yakobson
Messages: 1404
Registered: January 2010
|
Senior Member |
|
|
BlackSwan wrote on Fri, 11 January 2013 16:35That works OK for me!
Not always. It is better to use XXX_TAB_COLS, not XXX_TAB_COLUMNS:
SQL> desc tbl
Name Null? Type
----------------------------------------- -------- ------------
A VARCHAR2(10)
SQL> select column_name
2 from user_tab_columns
3 where table_name = 'TBL'
4 /
COLUMN_NAME
------------------------------
A
SQL> select column_name
2 from user_tab_cols
3 where table_name = 'TBL'
4 /
COLUMN_NAME
------------------------------
A
ADDRESS
SQL>
SY.
|
|
|
|
|
|
| Re: How to find tables which does not have specific columns [message #574559 is a reply to message #574553] |
Sat, 12 January 2013 13:48 |
Solomon Yakobson
Messages: 1404
Registered: January 2010
|
Senior Member |
|
|
Well, now I disagree  . It is, IMHO, better to have all info than part of it. To me:
SQL> select column_name,
2 virtual_column,
3 hidden_column
4 from user_tab_cols
5 where table_name = 'TBL'
6 /
COLUMN_NAME VIR HID
------------------------------ --- ---
A NO NO
ADDRESS YES YES
SQL>
privides more accurate picture. Querying user_tab_columns will not do you any good when you, for example, try to add ADDRESS column to table TBL and if fails:
SQL> alter table tbl
2 add address varchar2(20)
3 /
add address varchar2(20)
*
ERROR at line 2:
ORA-01430: column being added already exists in table
SQL> select column_name
2 from user_tab_columns
3 where table_name = 'TBL'
4 /
COLUMN_NAME
------------------------------
A
SQL>
SY.
[Updated on: Sat, 12 January 2013 13:49] Report message to a moderator |
|
|
|
| Re: How to find tables which does not have specific columns [message #574560 is a reply to message #574559] |
Sat, 12 January 2013 14:28 |
 |
Michel Cadot
Messages: 54225
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Then you ask the DBA to know why this error.
The DBA is there to make this kind of diagnosis.
But of course, it is not an error you have in production, it could be only in development and, as you have an history of the evolution of the table, you don't need the DBA to know why this error.
Regards
Michel
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
