-- dict.sql
-- Find data dictionary information by querying DICTIONARY with wildcard
-- and grouping the results into USER_/ALL_/DBA_/other.
-- William Robertson, www.williamrobertson.net

break on sort_key skip1 nodup
col sort_key noprint
col comments format a78 word

def table = &1

WITH dict_view AS
   ( SELECT CASE
               WHEN SUBSTR(table_name,1,5) = 'USER_' THEN 1
               WHEN SUBSTR(table_name,1,4) = 'ALL_' THEN 2
               WHEN SUBSTR(table_name,1,4) = 'DBA_' THEN 3
               ELSE 4
           END AS sort_key
         , table_name
         , comments
    FROM   dictionary
    WHERE  table_name LIKE UPPER('%&1%')
    UNION
    SELECT CASE
               WHEN SUBSTR(v.view_name,1,5) = 'USER_' THEN 1
               WHEN SUBSTR(v.view_name,1,4) = 'ALL_' THEN 2
               WHEN SUBSTR(v.view_name,1,4) = 'DBA_' THEN 3
               ELSE 4
           END AS sort_key
         , NVL(s.synonym_name,v.view_name)
         , NVL(d.comments,c.comments)
    FROM   dba_views  v
           LEFT OUTER JOIN dictionary d
               ON  d.table_name = v.view_name
           LEFT OUTER JOIN all_synonyms s
               ON  s.table_name = v.view_name
               AND s.table_owner = v.owner
               AND s.owner = 'PUBLIC'
           LEFT OUTER JOIN dba_tab_comments c
               ON  c.table_name = v.view_name
               AND c.owner = v.owner
    WHERE  (    v.view_name LIKE UPPER('V\_$%&1%') ESCAPE '\'
             OR v.view_name LIKE UPPER('GV\_$&1%') ESCAPE '\'
             OR v.view_name LIKE UPPER('DBA\_$&1%') ESCAPE '\' )
    AND    d.table_name IS NULL )
SELECT sort_key, table_name, comments
FROM   ( SELECT sort_key, table_name, comments
              , ROW_NUMBER() OVER(PARTITION BY table_name ORDER BY table_name NULLS LAST) AS seq
         FROM   dict_view )
WHERE  seq = 1
ORDER BY sort_key, table_name
/