OraFAQ Forum: SQL & PL/SQL » Listing the contents of the scott/tiger schema

Home » SQL & PL/SQL » SQL & PL/SQL » Listing the contents of the scott/tiger schema

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Listing the contents of the scott/tiger schema [message #256153]

Fri, 03 August 2007 03:48

greekozz
Messages: 8
Registered: August 2007

Junior Member

I would like to know if there is a command in order to see the contents of the scott/tiger schema.

if i use DESCRIBE i get only the contents of the given table, but i want to see
every table existing with its description like doing an ls -l or dir .

even better does anybody have a link where i can see all that in a
picture-diagram with also the relationships showing?

I hope i am not asking too much.

ps. i am using sqlplus on unix.

[Updated on: Fri, 03 August 2007 03:52]

Report message to a moderator

Re: Listing the contents of the scott/tiger schema [message #256155 is a reply to message #256153]

Fri, 03 August 2007 03:53

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Query dba_tables for tables or dba_objects for all objects.

Regards
Michel

Report message to a moderator

Re: Listing the contents of the scott/tiger schema [message #256171 is a reply to message #256155]

Fri, 03 August 2007 04:45

greekozz
Messages: 8
Registered: August 2007

Junior Member

Thanks Michael for the quick response.

I'am only learning sql the last 10 days so can you elaborate on that?
Like what do i have to enter at the command line.
sql>

Wink

Report message to a moderator

Re: Listing the contents of the scott/tiger schema [message #256175 is a reply to message #256171]

Fri, 03 August 2007 04:59

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

dba_tables and dba_objects are views.
Maybe you should not be able to query these ones as they required some privileges.
Then query user_tables or user_objects for your own objects.
Query means using SELECT statement.
Do you know how to use SELECT?

Regards
Michel

Report message to a moderator

Re: Listing the contents of the scott/tiger schema [message #256182 is a reply to message #256153]

Fri, 03 August 2007 05:52

greekozz
Messages: 8
Registered: August 2007

Junior Member

hey Michael

if i enter the describe command,ill get this pretty straight-forward readable result.

SQL> DESCRIBE EMP
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

If i do
SELECT * FROM user_tables

i get a LOT back, and i wouldn't use the word readable Smile

what i am asking is if there is a way to see the contents of the scott schema in a similar fashion.
Like:

emp
dept

and so on

Report message to a moderator

Re: Listing the contents of the scott/tiger schema [message #256183 is a reply to message #256182]

Fri, 03 August 2007 06:09

Frank
Messages: 7901
Registered: March 2000

Senior Member

learn some more sql Smile

Replace the * in select * by the columnname(s) you want to see

Report message to a moderator

Re: Listing the contents of the scott/tiger schema [message #256189 is a reply to message #256153]

Fri, 03 August 2007 06:33

greekozz
Messages: 8
Registered: August 2007

Junior Member

hey

))

i can't seem able to pass my message. I'll have to ask
some money return from my English teacher!! Smile

SQL> Unknown Command scott/tiger
 Table                                       No.Col    Names
 ----------------------------------------- -------- ----------------------------
EMP                                            8    EMPNO
                                                    ENAME
                                                    JOB
                                                    MGR
                                                    HIREDATE 
                                                    SAL 
                                                    COMM
                                                    DEPTNO 

DEPT                                            x   DEPTNO
                                                    .......

now is there anything that could produce this kind of outcome?
or similar?

[Updated on: Fri, 03 August 2007 06:35]

Report message to a moderator

Re: Listing the contents of the scott/tiger schema [message #256195 is a reply to message #256189]

Fri, 03 August 2007 06:53

Maaher
Messages: 7065
Registered: December 2001

Senior Member

A select statement with a bit fiddling could produce that outcome, yes. But user_tab_columns is all you need. It contains all data. I could provide you the query but I'm afraid it's a bit too complicated to understand immediately.

MHE

[Updated on: Sat, 04 August 2007 07:00]

Report message to a moderator

Re: Listing the contents of the scott/tiger schema [message #256198 is a reply to message #256189]

Fri, 03 August 2007 07:06

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes you can do that with select but it is more difficult.
First try to get the table_name from user_tables, then try to get column_name from dba_tab_columns.

Regards
Michel

Report message to a moderator

Re: Listing the contents of the scott/tiger schema [message #256199 is a reply to message #256198]

Fri, 03 August 2007 07:12

Maaher
Messages: 7065
Registered: December 2001

Senior Member

I would follow the advice Michel has given you Wink

MHE

Report message to a moderator

Re: Listing the contents of the scott/tiger schema [message #256210 is a reply to message #256199]

Fri, 03 August 2007 07:33

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

I will give you the sql which output two of your required columns and you solve the output for the third column.

good luck

  1  select case column_id when 1 then table_name else null end as table_name,
  2  column_name
  3* from user_tab_columns
SQL> /

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
DEPT                           DEPTNO
                               DNAME
                               LOC
EMP                            EMPNO
                               ENAME
                               JOB
                               MGR
                               HIREDATE
                               SAL
                               COMM
                               DEPTNO

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
BONUS                          ENAME
                               JOB
                               SAL
                               COMM
SALGRADE                       GRADE
                               LOSAL
                               HISAL

Report message to a moderator

Re: Listing the contents of the scott/tiger schema [message #256230 is a reply to message #256210]

Fri, 03 August 2007 08:27

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You gave the easiest part and let the more complicated one as an exercise.
You remind me one of my teacher... Smile

Regards
Michel

Report message to a moderator

Re: Listing the contents of the scott/tiger schema [message #256241 is a reply to message #256153]

Fri, 03 August 2007 09:15

greekozz
Messages: 8
Registered: August 2007

Junior Member

hey guys great feedback!!!

I hope you didn't expect me to reach this level in 10 days...did you?

now did my question sound strange?
Like it was the first thing that came on my mind after i followed few examples with the scott/tiger schema.
I wanted a visual representation of what i am working with.

now the query didn't work as i expected, probably because i work also with another demo database created afterwards so things got mixed up, but you gave me courage that when i have a better question you are the guys to talk to.

i'll just Describe manually table by table Wink

Report message to a moderator

Re: Listing the contents of the scott/tiger schema [message #256374 is a reply to message #256241]

Sat, 04 August 2007 01:40

Frank
Messages: 7901
Registered: March 2000

Senior Member

No, your question was not strange.
It's just that you have to realize that 'regular' sql returns rows with values in each column.
You only want to see values in certain columns (table name, number of columns) under specific conditions (only for the first row per table). That requires more complicated sql; more complicated than you would understand at the point you are.
Remember: the more formatting you want in your sql-output, the more complicated the sql gets.

Good luck with your next steps on the sql-path (and enjoy! It really is fun), and don't hesitate to come back when you get stuck.

Report message to a moderator

Previous Topic:	what is the command of object source
Next Topic:	problem in updating table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 08 06:24:14 CST 2024