Re: Hopefully easy question

From: Dan Clamage <clamage_at_mime.dw.lucent.com>
Date: 1997/09/25
Message-ID: <01bcc9c0$14175af0$54110b87_at_clamagent>

Dan Cradler <dcradler_at_mods.com> wrote in article <Pine.BSI.3.95.970925110319.22779B-100000_at_reno.modernsolutions.com>...
> Working with sqlplus, is there a way to see what tables, indexes, etc.
 are
> available in the current schema? I am used to Informix and there is an
> easy way to do this in their dbaccess utility. I've looked at the help
 and
> don't see any command that would list tables and indexes in sqlplus.

SELECT TABLE_NAME FROM USER_TABLES gives you the names of all tables you OWN.
SELECT TABLE_NAME FROM ALL_TABLES gives you the names of all tables you can ACCESS (including those other folks grant you access to) DESC <table name> will show all columns and their datatypes for tables listed in ALL_TABLES.

Below is a stored procedure that will describe all indexes for any given table (even if it's in another schema). Ask your DBA to compile it as SYS. Example run:
SET SERVEROUTPUT ON
SQL> EXECUTE GET_INDEX('DEPT');
INDEX INFORMATION FOR TABLE: DEPT:
>>OWNER: SCOTT; TABLESPACE: USER_DATA
........ INDEX: PK_DEPT UNIQUE

.............. DEPTNO                          NUMBER     (2,0)   NOT NULL

PL/SQL procedure successfully completed. Note: I was not logged on as SCOTT when I ran this.

/*
get index information
*/
CREATE OR REPLACE PROCEDURE get_index(Ptable_name IN dba_indexes.table_name%TYPE) IS

• global cursors
• finds one or more indexes for one specified table CURSOR main_index_cur(Ptable_name IN dba_indexes.table_name%TYPE) IS SELECT index_name, tablespace_name, owner, uniqueness FROM dba_indexes WHERE table_name = Ptable_name ORDER BY owner, index_name;
• finds one or more index columns for one specified index CURSOR index_column_cur(Ptable_name IN dba_ind_columns.table_name%TYPE, Pindex_name IN dba_ind_columns.index_name%TYPE, Powner IN dba_ind_columns.index_owner%TYPE) IS SELECT column_name FROM dba_ind_columns WHERE table_name = Ptable_name AND index_name = Pindex_name AND index_owner= Powner ORDER BY column_position;
• finds exactly one column CURSOR column_cur(Ptable_name IN dba_tab_columns.table_name%TYPE, Pcolumn_name IN dba_tab_columns.column_name%TYPE, Powner IN dba_tab_columns.owner%TYPE) IS SELECT data_type, data_length, data_precision, data_scale, nullable FROM dba_tab_columns WHERE owner = Powner AND table_name = Ptable_name AND column_name = Pcolumn_name;
• global variables main_index_rec main_index_cur%ROWTYPE; -- record variables index_column_rec index_column_cur%ROWTYPE; column_rec column_cur%ROWTYPE; status NUMERIC; -- trap exception old_owner dba_indexes.owner%TYPE := 'garbage'; -- look for control break index_cnt NUMERIC := 0; utable_name dba_indexes.table_name%TYPE; null_col VARCHAR2(8);

BEGIN
  DBMS_OUTPUT.enable(50000);
  utable_name := UPPER(Ptable_name); -- convert table name to upper case   DBMS_OUTPUT.put_line('INDEX INFORMATION FOR TABLE: ' || utable_name || ':');
  OPEN main_index_cur(utable_name);
  LOOP -- through all indexes for a table; handles same table different owners

    FETCH main_index_cur INTO main_index_rec;     EXIT WHEN main_index_cur%NOTFOUND;
    index_cnt := index_cnt + 1; -- found an index     IF (main_index_rec.owner != old_owner) THEN -- print this once

      old_owner := main_index_rec.owner;         -- set for next row
      DBMS_OUTPUT.put_line('>>OWNER: ' || main_index_rec.owner ||
                           '; TABLESPACE: ' ||

main_index_rec.tablespace_name);

    END IF; -- control break
    DBMS_OUTPUT.put_line('........ INDEX: ' || main_index_rec.index_name ||

                         '  ' || main_index_rec.uniqueness);
    OPEN index_column_cur(utable_name, main_index_rec.index_name,
                          main_index_rec.owner);
    LOOP
      FETCH index_column_cur INTO index_column_rec;
      EXIT WHEN index_column_cur%NOTFOUND; 
      OPEN column_cur(utable_name, index_column_rec.column_name,
                      main_index_rec.owner);
      FETCH column_cur INTO column_rec;
      CLOSE column_cur;
      IF column_rec.nullable = 'N' THEN
        null_col := 'NOT NULL';
      ELSE
        null_col := 'NULLABLE';
      END IF;
      IF (column_rec.data_precision IS NULL) THEN
        DBMS_OUTPUT.put_line('............... ' ||
                             RPAD(index_column_rec.column_name, 32) ||
                             RPAD(column_rec.data_type, 11) ||
                             '(' || TO_CHAR(column_rec.data_length) || ')'
||
                             '   ' || null_col);
      ELSE  -- a number
        DBMS_OUTPUT.put_line('............... ' ||
                             RPAD(index_column_rec.column_name, 32) ||
                             RPAD(column_rec.data_type, 11) ||
                             '(' || TO_CHAR(column_rec.data_precision) ||
',' ||
                             TO_CHAR(column_rec.data_scale) || ')' ||
                             '   ' || null_col);
      END IF;  -- display data size properly for type

    END LOOP; -- list of columns for this index     CLOSE index_column_cur;
  END LOOP; -- main_index
  CLOSE main_index_cur;

  IF (index_cnt = 0) THEN -- see if any indexes were found     DBMS_OUTPUT.put_line('>> NO INDEXES DEFINED');   END IF; EXCEPTION
WHEN OTHERS THEN
  BEGIN
    status := SQLCODE; -- trap error
    IF (main_index_cur%ISOPEN) THEN -- close any open cursors       CLOSE main_index_cur;
    END IF;
    IF (index_column_cur%ISOPEN) THEN
      CLOSE index_column_cur;
    END IF;
    IF (column_cur%ISOPEN) THEN
      CLOSE column_cur;
    END IF;
    DBMS_OUTPUT.put_line('SQL ERROR: ' || SQLERRM(status));   EXCEPTION
  WHEN OTHERS THEN
    NULL; -- avoid infinite loop
  END; END get_index; -- get index info
/

grant execute on get_index to public;
create public synonym get_index for get_index; Received on Thu Sep 25 1997 - 00:00:00 CEST