On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Wednesday, September 17, 2008

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

No comments:

Post a Comment