Home » SQL & PL/SQL » SQL & PL/SQL » How to find tables which does not have specific columns (Oracle 11g)
How to find tables which does not have specific columns [message #574533] Fri, 11 January 2013 15:09 Go to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Hi,

I need a query to find list all tables in a schema which does not have 'ADDRESS', 'CITY', 'STATE' columns.

Your prompt response is greatly appreciated.


Thanks
Re: How to find tables which does not have specific columns [message #574534 is a reply to message #574533] Fri, 11 January 2013 15:16 Go to previous messageGo to next message
BlackSwan
Messages: 21936
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Clarify selection criteria.
Should table be listed if it contains ADDRESS column but not the other two columns?
In other words how should columns be joined; with AND or OR operators?

please post expected Test Case!
Re: How to find tables which does not have specific columns [message #574535 is a reply to message #574534] Fri, 11 January 2013 15:19 Go to previous messageGo to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
it should list the table if any of the above columns are missing
Re: How to find tables which does not have specific columns [message #574536 is a reply to message #574535] Fri, 11 January 2013 15:32 Go to previous messageGo to next message
dariyoosh
Messages: 510
Registered: March 2009
Location: Iran / France
Senior Member
Take a look at all_tab_columns


Regards,
Dariyoosh
Re: How to find tables which does not have specific columns [message #574537 is a reply to message #574536] Fri, 11 January 2013 15:35 Go to previous messageGo to next message
BlackSwan
Messages: 21936
Registered: January 2009
Senior Member
dariyoosh wrote on Fri, 11 January 2013 13:32
Take a look at all_tab_columns


That works OK for me!
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1787
Registered: January 2010
Senior Member
BlackSwan wrote on Fri, 11 January 2013 16:35
That 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 #574553 is a reply to message #574550] Sat, 12 January 2013 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I disagree, it is better to use %_TAB_COLUMNS than %_TAB_COLS because you have not to see the hidden columns, for the application (and end users) they do not exist. They may only exist for DBA.

Regards
Michel
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1787
Registered: January 2010
Senior Member
Well, now I disagree Smile . 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 Go to previous message
Michel Cadot
Messages: 57599
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
Previous Topic: calling a if block based on parameter
Next Topic: Problem with comparing collections
Goto Forum:
  


Current Time: Wed Apr 16 04:45:50 CDT 2014

Total time taken to generate the page: 0.10093 seconds