-2

I have a oracle 10G data base and I want to find all the table name of which column name consist cl_ Is there any way to find all the table name I tried all_objects and all_source But failed. Please suggest some way to find them.

smn_onrocks
  • 1,282
  • 1
  • 18
  • 33
  • possible duplicate of [Oracle query to fetch column names](http://stackoverflow.com/questions/8739203/oracle-query-to-fetch-column-names) – Nir Alfasi Dec 31 '14 at 10:23
  • @alfasin no sir The query is little tricky Actualy I want to find any sollution through which I can rename a column name `cl_` to `ol_` but I am getting error message. – smn_onrocks Dec 31 '14 at 10:32
  • We don't know what is bigger picture about your question.. If you can explain brief then we can try to find ans for that. – Vignesh Shiv Dec 31 '14 at 10:36
  • 2
    If you're getting an error msg you should explain exactly what you're doing and what's the error. Our psychic abilities are limited... – Nir Alfasi Dec 31 '14 at 10:47

3 Answers3

1

Try this query in oracle:

SELECT * FROM USER_TAB_COLUMNS WHERE COLUMN_NAME like upper('%cl\_%') escape '\';
Hamidreza
  • 3,038
  • 1
  • 18
  • 15
1

ALL_TAB_COLS would give you the required details.

For example, I add a new table T to SCOTT schema, with column name as EMP_ID, I expect only 1 row in output for column name like 'EMP_%'

Let's see -

Edit Forgot to ESCAPE underscore.

SQL> CREATE TABLE t(EMP_ID NUMBER);

Table created.

SQL>
SQL> SELECT table_name, column_name
  2  FROM all_tab_cols
  3  WHERE owner='SCOTT'
  4  AND column_name LIKE 'EMP\_%' ESCAPE '\';

TABLE_NAME           COLUMN_NAME
-------------------- --------------
T                    EMP_ID

SQL>
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
0

all_tab_cols contains the details of all columns of tables owned by all users.

SELECT * FROM ALL_TAB_COLS
WHERE UPPER(column_name) like UPPER('%cl\_%') ESCAPE '\';--Returns details of all column names which has 'cl_' in its name

SELECT * FROM ALL_TAB_COLS
WHERE UPPER(column_name) like UPPER('cl\_%') ESCAPE '\';--Returns details of all column names which start with 'cl_' in its name

SELECT * FROM ALL_TAB_COLS
WHERE UPPER(column_name) like UPPER('%cl\_') ESCAPE '\';--Returns details of all column names which end with 'cl_' in its name

You can also try DBA_TAB_COLS.

Note : You can also use USER_TAB_COLS but it only returns the details of the columns of a table owned by the current user.

Hope this helps...!!!

Kishore Kumar
  • 78
  • 6