DICT - Data Dictionary

This query actually generates two reports. The first shows all tables starting with 'User_' (indicated by the 'U'), 'All_' (indicated by the 'A') or 'DBA_' (indicated by the 'D'). The 'base_name' is what I consider the portion past User/All/DBA. The report is sorted by this name. A page break occurs based on the first letter. A line break occurs based on the second part of this name, although those with just one row will be grouped together. The comment displayed for that base name is a random selection of the three, as I selected the 'Max' from whichever ones were available.

The second report shows those tables that do not start with 'User_', 'All_' or 'DBA'.

rem +--------------------------------------------------------------------------+
rem | Script ID:     Dic.sql
rem |                
rem | Purpose:       Display table names and comments based on User/All/DBA
rem |                
rem | Developer:     Lynn Tobias
rem | Script Date:   9/17/2008
rem | Oracle Ver:    10g
rem |
rem | Table(s) Used: Dict
rem |               
rem | Output:        Dic.Txt
rem |
rem | Revisions:     Dvl Date     Ver Comment/Change
rem |                --- -------- --- -----------------------------------------+
rem +--------------------------------------------------------------------------+

Clear Screen
Set Recsep Off Heading Off Newpage 1 Feedback Off Echo Off Linesize 200 Pagesize 200

Break On First_Char Skip Page -
      On Tbl_Cnt    Skip 1

Column Comments  Format A150
Column Userx     Format A1  Heading U
Column Allx      Format A1  Heading A
Column Dbax      Format A1  Heading D
Column Base_Name Format A26

Column First_Char New_Value xFirst_Char Noprint
Column Tbl_Cnt                          Noprint

Ttitle Left 'U A D ' xfirst_Char ' Whichever of the three comments was considered ''Max'' ' skip 1-
'- - - -------------------------- -----------------------------------------------------------------------------------------------------------'
Spool Dic.Txt

Prompt Tables from DICT Which Begin with User (U), All (A) or DBA (D)

--+- 1 -----------------------------------------------------------------
--| Pad the base name. Expand First_Char for title. We'll line-break on
--| the combination Part2+count.
--+---------------------------------------------------------------------
Select Userx, Allx, Dbax, 
                  Rpad(Base_Name,26,'.') As
       Base_Name, 
                  Rpad(First_Char,26,First_Char) As
       First_Char, 
                  Case When Tbl_Cnt = 1 
                       Then ' ' 
                       Else Part2||Tbl_Cnt 
                   End As
       Tbl_Cnt, 
       Comments 
  From 
      --+- 1.1 --------------------------------------------------------
      --| I want to bunch all the separate ones together without a line
      --| break, so count to see how many there are of each 2nd division.
      --+--------------------------------------------------------------
      (
      Select Userx, Allx, Dbax, 
             Base_Name, 
             First_Char, 
             Part2, 
                     Count(Base_Name) Over (Partition By Part2) 
             Tbl_Cnt, 
             Comments
        From 
            --+- 1.1.1 -----------------------------------------------
            --| Max everything to get one line per base name. Pick up
            --| a random comment (hopefully the 3 are similar).
            --| To keep 'Tablespaces' and Tablespace' together, remove
            --| any ending 'S' from part2.
            --+-------------------------------------------------------
            (
            Select           Max(Case When Part1 = 'USER' Then 'U' Else '.' End) As
                   Userx, 
                             Max(Case When Part1 = 'ALL'  Then 'A' Else '.' End) As
                   Allx, 
                             Max(Case When Part1 = 'DBA'  Then 'D' Else '.' End) As
                   Dbax, 
                             Max(First_Char) As
                   First_Char,
                   Base_Name, 
                             Max(Case When Part2 Like '%S' 
                                      Then Substr(Part2,1,Length(Part2)-1) 
                                      Else Part2 
                                  End) As
                   Part2,
                             Max(Comments) As
                   Comments
            From 
                --+- 1.1.1.1 --------------------------------------
                --| Part1 - User/All/DBA           (and comments)
                --| Base_Name - Name other than User/All/DBA
                --| First_Char of Base - used for page breaks
                --| Part2 - the 2nd portion used for line breaks
                --+------------------------------------------------
                (
                Select                Substr(Table_Name,1,U1-1)        As 
                       Part1, 
                                      Substr(Table_Name,U1+1)          As
                       Base_Name, 
                                      Substr(Table_Name,U1+1,1)        As 
                       First_Char, 
                                      Substr(Table_Name,U1+1, U2-U1-1) As
                       Part2, 
                       Comments
                  From  
                      --+- 1.1.1.1.1 -----------------------------
                      --| Keep the table_name and comment and get
                      --| the position of the first two underscores.
                      --| Remove the random line break from the comments.
                      --+-----------------------------------------
                      (
                      Select Table_Name, 
                                        Instr(Table_Name,'_') 
                             U1, 
                                        Instr(Table_Name||'_','_',1,2) 
                             U2, 
                                        Replace(Comments,Chr(10),' ') 
                             Comments
                        From Dict
                       Where (Table_Name Like 'DBA_%' Or 
                              Table_Name Like 'ALL_%' Or 
                              Table_Name Like 'USER_%')
                       And Nvl(Comments,' ')   Not Like 'Synonym for V_$%'
                       And Nvl(Comments,' ')   Not Like 'Synonym for GV_$%'
                      )
                --+- 1.1.1.1 End ----------------------------------
                Order By Base_Name
                )
            --+- 1.1.1 End -------------------------------------------
            Group By Base_Name
            Order By Base_Name
            )
      --+- 1.1 End ----------------------------------------------------
      Order By Part2, Base_Name
      )
--+- 1 End -------------------------------------------------------------
/
Ttitle Left 'Tables not starting with User, All or DBA' skip 2
Set Heading On
Column Comments Heading Comments
--+- 2 -----------------------------------------------------------------
--| Print the tables not starting with User/All/DBA
--+---------------------------------------------------------------------
Select Table_Name,
                  Replace(Comments,Chr(10),' ') As
       Comments
  From Dict
 Where Upper(Comments) Not Like 'SYNONYM%'
   And Table_Name Not Like 'DBA_%'
   And Table_Name Not Like 'ALL_%'
   And Table_Name Not Like 'USER_%'
 Order by Table_Name
/
Spool Off
Clear Columns
Set Recsep Wrapped Newpage 1 Feedback On