Search an Oracle Database for Tables with Specific Column Names

Search an Oracle database for tables with specific column names?

To find all tables with a particular column:

select owner, table_name from all_tab_columns where column_name = 'ID';

To find tables that have any or all of the 4 columns:

select owner, table_name, column_name
from all_tab_columns
where column_name in ('ID', 'FNAME', 'LNAME', 'ADDRESS');

To find tables that have all 4 columns (with none missing):

select owner, table_name
from all_tab_columns
where column_name in ('ID', 'FNAME', 'LNAME', 'ADDRESS')
group by owner, table_name
having count(*) = 4;

How to query specific column name from multiple table from SQL

Because user_tab_columns has table_name and column_name in each rows, so your select only omit the row with that columns, not other rows with other columns of that table.

One query you could use is:

SELECT DISTINCT table_name
FROM user_tab_columns c
WHERE NOT EXISTS (
    SELECT 1
    FROM user_tab_columns
    WHERE column_name LIKE '%OBJECT_NO%'
        AND table_name = c.table_name
) 
ORDER BY table_name;

Other way could work is:

SELECT DISTINCT table_name
FROM user_tab_columns 
WHERE table_name NOT IN (
    SELECT table_name
    FROM user_tab_columns
    WHERE column_name LIKE '%OBJECT_NO%'
) 
ORDER BY table_name;

Oracle query to fetch column names

The Oracle equivalent for information_schema.COLUMNS is USER_TAB_COLS for tables owned by the current user, ALL_TAB_COLS or DBA_TAB_COLS for tables owned by all users.

Tablespace is not equivalent to a schema, neither do you have to provide the tablespace name.

Providing the schema/username would be of use if you want to query ALL_TAB_COLS or DBA_TAB_COLS for columns OF tables owned by a specific user. in your case, I'd imagine the query would look something like:

String sqlStr= "
SELECT column_name
  FROM all_tab_cols
 WHERE table_name = 'USERS'
   AND owner = '" +_db+ "'
   AND column_name NOT IN ( 'PASSWORD', 'VERSION', 'ID' )"

Note that with this approach, you risk SQL injection.

EDIT: Uppercased the table- and column names as these are typically uppercase in Oracle; they are only lower- or mixed case if created with double quotes around them.

Oracle how to find all column that match a particular column name

... all columns within a database whose name matches with a given input column and whose datatype matches with the given input column

You can use the all_tab_columns data dictionary view. For example, to select all columns of type NUMBER and where the column name starts with "A", use something like

SELECT table_name, column_name, data_type 
FROM all_tab_columns 
WHERE data_type='NUMBER' AND column_name like 'A%';

TABLE_NAME             COLUMN_NAME            DATA_TYPE
---------------------- ------- ------------------------------
AUDIT_ACTIONS          ACTION                 NUMBER
SDO_CS_SRS             AUTH_SRID              NUMBER
APEX_WS_APP_PAGES      APPLICATION_ID         NUMBER
...

SQL Oracle search for column name and schema

Just add OWNER to your select list columns:

    select distinct owner, table_name, column_name

from all_tab_columns

where column_name like '%APPOINTMENT%'

How to search a column name within all tables of a database

select table_name, column_name
from user_tab_columns
where column_name like '%AUTHOR%';

All system views are documented here: http://docs.oracle.com/cd/E11882_01/server.112/e25513/toc.htm

---

Related Topics