Home » SQL & PL/SQL » SQL & PL/SQL » Listing the contents of the scott/tiger schema
Listing the contents of the scott/tiger schema [message #256153] Fri, 03 August 2007 03:48 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query dba_tables for tables or dba_objects for all objects.


Regards
Michel
Re: Listing the contents of the scott/tiger schema [message #256171 is a reply to message #256155] Fri, 03 August 2007 04:45 Go to previous messageGo to next message
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
Re: Listing the contents of the scott/tiger schema [message #256175 is a reply to message #256171] Fri, 03 August 2007 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Listing the contents of the scott/tiger schema [message #256182 is a reply to message #256153] Fri, 03 August 2007 05:52 Go to previous messageGo to next message
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

Re: Listing the contents of the scott/tiger schema [message #256183 is a reply to message #256182] Fri, 03 August 2007 06:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
learn some more sql Smile

Replace the * in select * by the columnname(s) you want to see
Re: Listing the contents of the scott/tiger schema [message #256189 is a reply to message #256153] Fri, 03 August 2007 06:33 Go to previous messageGo to next message
greekozz
Messages: 8
Registered: August 2007
Junior Member
hey Smile))

i can't seem able to pass my message. I'll have to ask
some money return from my English teacher!! Smile 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 Go to previous messageGo to next message
Maaher
Messages: 7062
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Listing the contents of the scott/tiger schema [message #256199 is a reply to message #256198] Fri, 03 August 2007 07:12 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I would follow the advice Michel has given you Wink

MHE
Re: Listing the contents of the scott/tiger schema [message #256210 is a reply to message #256199] Fri, 03 August 2007 07:33 Go to previous messageGo to next message
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


Re: Listing the contents of the scott/tiger schema [message #256230 is a reply to message #256210] Fri, 03 August 2007 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Listing the contents of the scott/tiger schema [message #256241 is a reply to message #256153] Fri, 03 August 2007 09:15 Go to previous messageGo to next message
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

Re: Listing the contents of the scott/tiger schema [message #256374 is a reply to message #256241] Sat, 04 August 2007 01:40 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: what is the command of object source
Next Topic: problem in updating table
Goto Forum:
  


Current Time: Sat Dec 03 10:03:02 CST 2016

Total time taken to generate the page: 0.14191 seconds