Script for describing users database (no request, this is it)

From: Harkki Jukka <jht_at_cs.tut.fi>
Date: Fri, 24 Sep 1993 09:59:26 GMT
Message-ID: <1993Sep24.095926.1287_at_news.cs.tut.fi>


   Hello!

   This is a small utility script, which describes the database    of one user. It's made for Oracle V6 Data Dictionary.

   I have found this script useful when I have different version    of particular database or application installed. Using this script    and diff I can see what has to be converted to get the database    up to date. It can also be used to examine objects of one user.

   As usually, this script is delivered AS IS with no quarantees.    Also, there are no limitations using this script. You can even    kill it ;-) The script is poorly commented, but who needs    comments because code is obvious (actually I did not want to    write comments again in English and the most of you would have    done nothing with Finnish comments).

   Hope somebody finds this useful...

   Jukka

  • cut here --- cut here --- cut here --- cut here --- cut here --- rem ================================================================= rem Describing the database of one user. Objects of logging user rem and Public Synonyms pointing to users objects are listed. rem rem jht_at_cs.tut.fi 23.9.1993 rem =================================================================

DEFINE f_spool='descdb.lst';

SET PAGESIZE 0;
SET HEADING OFF;
SET VERIFY OFF;
SET TERMOUT OFF;
SET FEEDBACK OFF;

rem =================================================================
rem Temporary table for building the report.
rem =================================================================

COLUMN table_name NOPRINT NEW_VALUE table_name; SELECT 'TMP_' || USERENV('SESSIONID') "table_name" FROM SYSTEM.DUAL; DROP TABLE &table_name;
CREATE TABLE &table_name (

   row_no NUMBER,
   text CHAR(132) );

rem =================================================================
rem Building the report with PL/SQL
rem =================================================================

DECLARE

   prev_object_type CHAR(13);
   s_object_type    CHAR(13);
   s_object_name    CHAR(30);

   s_column_name    CHAR(30);
   s_data_type      CHAR(9);
   s_data_length    NUMBER;
   s_data_precision NUMBER;
   s_data_scale     NUMBER;
   s_nullable       CHAR(8);

   s_table_owner    CHAR(30);
   s_unique         CHAR(30);

   s_table_name     CHAR(30);
   s_db_link        CHAR(30);

   s_min_value      NUMBER;
   s_max_value      NUMBER;
   s_increment_by   NUMBER;
   s_cycle          CHAR(3);
   s_order          CHAR(3);

   s_synonym_name CHAR(30);

   s_row_no         NUMBER := 1;
   s_text           CHAR(132);
   n_column         NUMBER;
   n_row            NUMBER;

   CURSOR fetch_object IS
      SELECT object_type, object_name

   FROM user_objects
   WHERE object_name != '&table_name'
   ORDER BY object_type, object_name;

   CURSOR fetch_public_synonym IS

      SELECT synonym_name, table_owner, table_name, db_link    FROM all_synonyms
   WHERE owner = 'PUBLIC'
     AND table_owner = USER
   ORDER BY synonym_name;

   CURSOR fetch_table_col IS

      SELECT column_name, data_type, data_length, data_precision,
             data_scale,
             RPAD(DECODE(nullable,'N','Not NULL','Y',' ',nullable),8)
   FROM user_tab_columns
   WHERE table_name = s_object_name
   ORDER BY column_id;

   CURSOR fetch_index IS

      SELECT table_name,
             DECODE(uniqueness,'NONUNIQUE',NULL,uniqueness || ' ')
   FROM user_indexes
   WHERE index_name = s_object_name;

   CURSOR fetch_index_col IS

      SELECT column_name
   FROM user_ind_columns
   WHERE index_name = s_object_name
     AND table_name = s_table_name
   ORDER BY column_position;

   CURSOR fetch_sequence IS

      SELECT min_value, max_value, increment_by,
             DECODE(cycle_flag,'N','No','Y','Yes',cycle_flag), 
             DECODE(order_flag,'N','No','Y','Yes',order_flag)
   FROM user_sequences
   WHERE sequence_name = s_object_name;

   CURSOR fetch_synonym IS

      SELECT table_owner, table_name, db_link    FROM user_synonyms
   WHERE synonym_name = s_object_name;
BEGIN
   OPEN fetch_object;
   LOOP

      FETCH fetch_object INTO s_object_type, s_object_name;
      EXIT WHEN fetch_object%NOTFOUND;

      IF s_row_no > 1 THEN
         IF s_object_type IN ('TABLE','VIEW') OR
            prev_object_type != s_object_type THEN
            INSERT INTO &table_name VALUES (s_row_no, NULL);
            s_row_no := s_row_no + 1;
         END IF;
      END IF;

      IF s_object_type IN ('TABLE','VIEW') THEN
         INSERT INTO &table_name VALUES (
            s_row_no, INITCAP(s_object_type) || ' ' || LOWER(s_object_name));
         s_row_no := s_row_no + 1;
         OPEN fetch_table_col;
         LOOP
            FETCH fetch_table_col INTO
               s_column_name, s_data_type, s_data_length,
               s_data_precision, s_data_scale, s_nullable;
            EXIT WHEN fetch_table_col%NOTFOUND;
            s_text := '   ' || RPAD(LOWER(s_column_name),30);
            s_text := s_text || ' ' || s_nullable;
            s_text := s_text || ' ' || INITCAP(s_data_type);
            IF s_data_type = 'CHAR' THEN
               s_text := s_text || '(' || TO_CHAR(s_data_length) || ')';
            ELSIF s_data_type = 'NUMBER' THEN
               IF s_data_precision > 0 THEN
                  s_text := s_text || '(' || TO_CHAR(s_data_precision);
                  IF s_data_scale > 0 THEN
                     s_text := s_text || ',' || TO_CHAR(s_data_scale);
                  END IF;
                  s_text := s_text || ')';
               END IF;
            END IF;

            INSERT INTO &table_name VALUES (s_row_no, s_text);
            s_row_no := s_row_no + 1;

         END LOOP;
         CLOSE fetch_table_col;

      ELSIF s_object_type = 'INDEX' THEN
         s_text := 'Index ' || LOWER(s_object_name);
         OPEN fetch_index;
         FETCH fetch_index INTO s_table_name, s_unique;
         IF fetch_index%FOUND THEN
            s_text := s_text || ': ' || INITCAP(s_unique) || 'on ' ||
                      LOWER(s_table_name);

            n_column := 0;
            OPEN fetch_index_col;
            LOOP
               FETCH fetch_index_col INTO s_column_name;
               EXIT WHEN fetch_index_col%NOTFOUND;
               n_column := n_column + 1;
               IF n_column = 1 THEN
                  s_text := s_text || ' (' || LOWER(s_column_name);
               ELSE
                  s_text := s_text || ', ' || LOWER(s_column_name);
               END IF;
            END LOOP;
            CLOSE fetch_index_col;
            IF n_column > 0 THEN
               s_text := s_text || ')';
            END IF;
         END IF;
         CLOSE fetch_index;

         INSERT INTO &table_name VALUES (s_row_no, s_text);
         s_row_no := s_row_no + 1;

      ELSIF s_object_type = 'SEQUENCE' THEN
         s_text := 'Sequence ' || LOWER(s_object_name);
         OPEN fetch_sequence;
         FETCH fetch_sequence INTO
            s_min_value, s_max_value, s_increment_by, s_cycle, s_order;
         IF fetch_sequence%FOUND THEN
            s_text := s_text || ': ' || TO_CHAR(s_min_value) || '-' ||
                                        TO_CHAR(s_max_value) || '++' ||
                                        TO_CHAR(s_increment_by) ||
                                        ' Cycle: ' || s_cycle ||
                                        ' Order: ' || s_order;
         END IF;
         CLOSE fetch_sequence;
         INSERT INTO &table_name VALUES (s_row_no, s_text);
         s_row_no := s_row_no + 1;

      ELSIF s_object_type = 'SYNONYM' THEN
         s_text := 'Synonym ' || LOWER(s_object_name);
         OPEN fetch_synonym;
         FETCH fetch_synonym INTO s_table_owner, s_table_name, s_db_link;
         IF fetch_synonym%FOUND THEN
            s_text := s_text || ' for ' ||
                      LOWER(s_table_owner) || '.' || LOWER(s_table_name);
            IF s_db_link IS NOT NULL THEN
               s_text := s_text || '_at_' || LOWER(s_db_link);
            END IF;
         END IF;
         CLOSE fetch_synonym;
         INSERT INTO &table_name VALUES (s_row_no, s_text);
         s_row_no := s_row_no + 1;

      ELSE
         INSERT INTO &table_name VALUES (
            s_row_no, INITCAP(s_object_type) || ' ' || LOWER(s_object_name));
         s_row_no := s_row_no + 1;
      END IF;

      prev_object_type := s_object_type;
   END LOOP;
   CLOSE fetch_object;

   n_row := 0;
   OPEN fetch_public_synonym;
   LOOP

      FETCH fetch_public_synonym INTO 
         s_synonym_name, s_table_owner, s_table_name, s_db_link;
      EXIT WHEN fetch_public_synonym%NOTFOUND;
      s_text := 'Public Synonym ' || LOWER(s_synonym_name) || ' for ' ||
                LOWER(s_table_name);
      IF s_db_link IS NOT NULL THEN
         s_text := s_text || '_at_' || LOWER(s_db_link);
      END IF;

      IF n_row = 0 THEN
         INSERT INTO &table_name VALUES (s_row_no, NULL);
         s_row_no := s_row_no + 1;
         n_row := 1;
      END IF;

      INSERT INTO &table_name VALUES (s_row_no, s_text);
      s_row_no := s_row_no + 1;

   END LOOP;
   CLOSE fetch_public_synonym;

   COMMIT;
END;
/

rem =================================================================
rem Running the report itself.
rem =================================================================

COLUMN text FORMAT a80;
SPOOL &f_spool;

SELECT text
FROM &table_name
ORDER BY row_no;

SPOOL OFF;

rem =================================================================
rem Cleaning up.
rem =================================================================

DROP TABLE &table_name;

QUIT;
--- cut here --- cut here --- cut here --- cut here --- cut here ---

-- 
//
//  Jukka Harkki   Net: jht_at_cs.tut.fi      Addr: Joinex Oy
//                 Tel: +358 31 222 4680         Pyh{j{rvenkatu 5B
//                 Fax: +358 31 222 4685         33200 TAMPERE, FINLAND
Received on Fri Sep 24 1993 - 11:59:26 CEST

Original text of this message