Output All Column Names 3

[boggg1]

I'm looking for a column in a large Oracle 8i database which contains certain data so I can write queries to it. I don't know the column name or the table name but I am hoping I would be able to identify the column name if I saw it. The problem is that there are hundreds of tables in the database and therefore thousands of columns to interrogate. DESCRIBE tablename in SQL+ would work but it would be very tedious. Is it possible to get a list of all column names in all tables in the database ?. I have SQL, SQL+ and Access experience but no pure database administration knowledge and read only privelages so treat me gently !

Boggg1

 

[hoinz]

try this:
select column_name, table_name from cols;

 

[boggg1]

hoinz..
I get no rows selected

Excuse my lack of knowledge here but what is "cols" ? Am I supposed to have substituted "cols" with something ?

Boggg1

 

[KenCunningham]

cols should contain details of tables and associated columns as hoinz suggests. Seems strange if no rows are returned. Can you desc cols?

 

[hoinz]

what do you get from this:
desc cols

 

[boggg1]

SQL> select column_name, table_name from cols;

no rows selected

SQL> desc cols
Name Null? Type
------------------------------- -------- ----
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
DATA_TYPE VARCHAR2(106)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(30)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NOT NULL NUMBER
DEFAULT_LENGTH NUMBER
DATA_DEFAULT LONG
NUM_DISTINCT NUMBER
LOW_VALUE RAW(32)
HIGH_VALUE RAW(32)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER

Boggg1

 

[hoinz]

It seems there are no tables you are allowed to read.
Can you select from any table?

 

[boggg1]

Just ran a standard "SELECT" query I had previously written in SQL and it gave 920 rows as expected.

Boggg1

 

[hoinz]

Hm, ...,
strange, I'm not sure about this ...
maybe you haven't got the privilege to see some tables contributing to cols. And this may be a difficult task to fix.
Can you log in as some other oracle user, and query cols then?

 

[boggg1]

Aha,

It seems there is a view containing the information you were expecting to be in cols. Strangely USER_TAB_COLUMNS is empty but ALL_TAB_COLUMNS is populated and seems to be what cols should have been.

Anyway, your basic method worked even if the table names had to be changed. Thanks for your efforts, have a star each.

Boggg1

 

[lewisp]

Jumped in late on this one......

Isnt cols a view of USER_TAB_COLUMNS, therefore only tables owned by the current user will be visible?

Try this bit of SQLPLUS:

BREAK ON table_name SKIP 1
SET PAGES 0

SELECT table_name,
       column_name
FROM   all_tab_columns
WHERE  owner = <owner>
ORDER BY 1,2

 

[hoinz]

Hi,

thanks, Boggg1, glad to see that my idea helped.

And like Lewis, I am confused, as COLS should show your tables from ALL_TAB_COLUMNS.
One possible explanation for this:
You don't own any tables at all, and those you are selecting from are owned by some other user. (And you may have a synonym for it.)
But anyways, ALL_TAB_COLUMNS should show that.

regards

 

[boggg1]

Not quite sure what "owner" means in my case because it seems to have various names in it (including the database name or SYS in some cases). I am certainly not the owner so (if your are right lewisp) that explains why cols is empty. Sorry hoinz, perhaps I should have made it clear that I only interrogate the critter.

Anyway lewisp, your...
BREAK ON table_name SKIP 1
SET PAGES 0
has encouraged me to learn a bit about SQL*Plus formatting as your format is very neat.

I will build a permanent query in my prefered Visual Basic to allow me to search again when I want to.

I've got what I need, thanks to you all for your valuable time.

Boggg1

 

[boggg1]

PS... Have a star lewisp

Boggg1