OraFAQ Forum: SQL & PL/SQL » oracle equivalent to sp

Home » SQL & PL/SQL » SQL & PL/SQL » oracle equivalent to sp_help?

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

oracle equivalent to sp_help? [message #9730]

Tue, 02 December 2003 09:59

nimmi smith
Messages: 1
Registered: December 2003

Junior Member

I am an SQL DBA, newbie to Oracle. Does Oracle have an equivalent to SQL server's stored proc sp_help? In SQL server, sp_help reports information about a database object (any object listed in the sysobjects table), a user-defined data type, or a data type supplied by Microsoft� SQL Server�.

Report message to a moderator

Re: oracle equivalent to sp_help? [message #9750 is a reply to message #9730]

Wed, 03 December 2003 00:18

Maaher
Messages: 7065
Registered: December 2001

Senior Member

You have the data dictionary, a set of views that can show you whatever you need: a lot come in 3 flavors: USER_xxx views display the information about the current users' objects, ALL_xxx views display info about the objects the user has access to and DBA_xxx are for DBA users and show info about *ALL* objects.

e.g.:

USER_TABLES contains info about all the tables of which the current user is owner, that is: they are created in his schemat

DBA_VIEWS contains info about all the views in the database.

ALL_TAB_COLUMNS contains column information about all the tables the user has access to (SELECT grants).

DBA_OBJECTS contains info about all the objects in the database.

The list is quite large and can be found in the Oracle documentation at docs.oracle.com or tahiti.oracle.com. These site need a free OTN account (subscription, no strings attached).

Useful links are this and this from tahiti.oracle.com. Especially the last link is interesting since it is a complete list.

HTH,
MHE

Report message to a moderator

Re: oracle equivalent to sp_help? [message #9759 is a reply to message #9730]

Wed, 03 December 2003 10:30

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Full-featured development tools like TOAD provide this information easily, but even in SQL*Plus you can use the DESC command to get some basic information about most objects:

-- What is the structure of this table/view?
 
sql>desc user_sequences
 Name                                     Null?    Type
 ---------------------------------------- -------- ---------------------------
 SEQUENCE_NAME                            NOT NULL VARCHAR2(30)
 MIN_VALUE                                         NUMBER
 MAX_VALUE                                         NUMBER
 INCREMENT_BY                             NOT NULL NUMBER
 CYCLE_FLAG                                        VARCHAR2(1)
 ORDER_FLAG                                        VARCHAR2(1)
 CACHE_SIZE                               NOT NULL NUMBER
 LAST_NUMBER                              NOT NULL NUMBER
 
-- What is this user-defined datatype?
 
sql>desc numbertable
 NUMBERTABLE TABLE OF NUMBER

Report message to a moderator

Previous Topic:	Working with libraries in Forms 6.0
Next Topic:	DBMS_JOB - Run Daily

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 18 18:42:45 CDT 2025