Home » SQL & PL/SQL » SQL & PL/SQL » oracle equivalent to sp_help?
oracle equivalent to sp_help? [message #9730] Tue, 02 December 2003 09:59 Go to next message
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™.
Re: oracle equivalent to sp_help? [message #9750 is a reply to message #9730] Wed, 03 December 2003 00:18 Go to previous messageGo to next message
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
Re: oracle equivalent to sp_help? [message #9759 is a reply to message #9730] Wed, 03 December 2003 10:30 Go to previous message
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
Previous Topic: Working with libraries in Forms 6.0
Next Topic: DBMS_JOB - Run Daily
Goto Forum:
  


Current Time: Thu Mar 28 16:15:44 CDT 2024