-
Oracle SQL Developer Describe Command
Hi Everybody,
I have a database for a fictional school. It contains tables for faculty, students, course, section, term, enrollment, etc.
I am trying to write a script that provides all of the information in, and duplicates the formatting of, Oracle's SQL*Plus describe command. The output should add the comments on the rows. the input is: owner (system) and table name (term (or any of my tables)). Output should be: columns for Name, Null?, Type, Comments.
I enter "system" as owner and Term for table, but the only thing returned is the name of the table, not the other information I need.
My script looks like this:
SELECT table_name
FROM all_tables
WHERE owner = UPPER('&schemaowner')
AND table_name LIKE UPPER('%&table%');
This is the output:
old:SELECT table_name
FROM all_tables
WHERE owner = UPPER('&schemaowner')
AND table_name LIKE UPPER('%&table%')
new:SELECT table_name
FROM all_tables
WHERE owner = UPPER('system')
AND table_name LIKE UPPER('%term%')
TABLE_NAME
------------------------------
TERM
Thank you
-
Have you looked at either dba_tab_columns or user_tab_columns? Also if you are looking at objects in the schema
that you are logged in as don't use the all_* views, use the user_* views. As in user_tables, user_tab_columns.
Between user_tables, user_tab_columns and possibly user_cons_columns you should have everything that you need.
There is also user_ind_columns in case that helps too.
-
thanks for getting back to me Gandolf.
First of all your suggestions might be good, but I am a bit confused.
Yes, I, logged in a system. I'm not sure where to put your suggestions. Are you saying user_*views should be in the select statement? I'm not really sure whay my query should look like. I'm rather new at this.
Thanks for your help.
-
Which schema owns the objects that you are looking at? If you are logged in as system, then you should
use the dba_* views. Such as dba_tables, dba_tab_columns, dba_cons_columns and dba_ind_columns.
From a design perspective, you should not create any objects in sys or system.
-
Well, Gandolf, I guess I just don't get it. It seems like it should be a simple problem, but no matter what i try I get an error, or just the name of the table is returned. as in:
SELECT table_name
FROM dba_tables
WHERE owner = UPPER('&schemaowner')
AND table_name LIKE UPPER('%&table%');
Output: term (that was the name of the table I entered at the prompt)
-
typo
if you are looking for the columns and their attributes you should be selecting from dba_tab_columns, filtering by table_name and sorting by table_name if selecting more than one table.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|