Re: SQL

Shivram Vempaty (svemp_at_ctt.bellcore.com) wrote:
: Group,
 

: I would like to know what would be the sql syntax of getting the
: table description(s),triggers, indexes in different owner's
: schema excluding mine.
 

: Thanks in advance
: SV

Dear Shivram,

One way I can think of to get each tables description, its triggers and its indexes is to write a PL/SQL block that loops on a cursor defined on the table ALL_TABLES. You should have access to the tables defined in different user accounts.

Define a cursor to retrieve TABLE_NAMEs. Order By Owner, Table_name. Lets call this cursor All_Tables_Cursor.

For each Rec in All_Tables_Cursor
 Retrieve Table comments -- Use A Cursor on ALL_TAB_COMMENTS  Retrieve Columns
 For Each Column
  Loop
   Retrieve Column comments -- Use a Cursor on ALL_COL_COMMENTS   End Loop
 Retrieve Index Names -- Use a cursor on ALL_INDEXES  For each Index Name
  Loop
   Retrieve Index Columns -- Use a cursor on ALL_IND_COLUMNS   End Loop Retrieve Index Name
 Retrieve Triggers -- Use a cursor on ALL_TRIGGERS  Retrieve Constraints -- Use a cursor on ALL_CONSTRAINTS  For each Constraint
  Loop
    Retrieve Constraint Details -- Use a Cursor on ALL_CONS_COLUMNS   End Loop Constraint
End Loop

I have not written the exact syntax since that would take long. But email me if you need the exact syntax and I'll try and help you out.

Hope this helps.

--
---------------------------------------------------------------------------
Kannan 
Email: skannan_at_fs.com
Mastech Systems Corporation

The above are my own comments and opinion. They do not purport that of 
anybody else.
       ** Different is Not Better. Better is not Different. **
---------------------------------------------------------------------------