-
Notifications
You must be signed in to change notification settings - Fork 0
/
column_details.sql
197 lines (197 loc) · 19.4 KB
/
column_details.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
REM *************************************************************************
REM AUTHOR: Jeff Moss
REM NAME: column_details.sql
REM
REM *************************************************************************
REM
REM Purpose:
REM This script shows details from DBA_TAB_COLS and DBA_TAB_COL_STATISTICS
REM for a given Owner/Table Name
REM
REM Change History
REM
REM Date Author Description
REM =========== ================= ================================================
REM 25-NOV-2011 Jeff Moss Initial Version
REM
REM *************************************************************************
SELECT t.COLUMN_name
, DECODE(t.data_type
,'NUMBER',t.data_type||'('||
DECODE(t.data_precision
,NULL,t.data_length||')'
,t.data_precision||','||t.data_scale||')')
,'DATE',t.data_type
,'LONG',t.data_type
,'LONG RAW',t.data_type
,'ROWID',t.data_type
,'MLSLABEL',t.data_type
,t.data_type||'('||t.data_length||')') ||' '||
DECODE(UPPER(t.nullable)
,'N','NOT NULL'
,NULL) col
, t.num_distinct
, t.num_nulls
, tcs.histogram
, t.density
, DECODE(t.low_value,NULL,'N/A'
,DECODE(t.data_type
,'DATE'
,LPAD(TO_CHAR(TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),8,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),8,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),7,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),7,1))) * 16),2,'0')||'-'||
LPAD(TO_CHAR(TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),6,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),6,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),5,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),5,1))) * 16),2,'0')||'-'||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),2,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),2,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),1,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),1,1))) * 16) - 100),2,'0')||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),4,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),4,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),3,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),3,1))) * 16) - 100),2,'0')||' '||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),10,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),10,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),9,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),9,1))) * 16) - 1),2,'0')||':'||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),12,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),12,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),11,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),11,1))) * 16) - 1),2,'0')||':'||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),14,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),14,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.low_value),13,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),13,1))) * 16) - 1),2,'0')
,'NUMBER'
,DECODE(SIGN(NVL(LENGTH(rawtohex(t.low_value)),0) - 3),-1,''
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),4,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),4,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),3,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),3,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.low_value)),0) - 5),-1,''
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),6,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),6,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),5,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),5,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.low_value)),0) - 7),-1,''
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),8,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),8,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),7,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),7,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.low_value)),0) - 9),-1,''
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),10,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),10,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),9,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),9,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.low_value)),0) - 11),-1,''
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),12,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),12,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),11,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),11,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.low_value)),0) - 13),-1,''
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),14,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),14,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),13,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),13,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.low_value)),0) - 15),-1,''
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),16,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),16,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.low_value),15,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.low_value),15,1))) * 16) - 1)
),2,'0'))
,'N/A'
)) low_value
, DECODE(t.high_value,NULL,'N/A'
,DECODE(t.data_type
,'DATE'
,LPAD(TO_CHAR(TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),8,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),8,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),7,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),7,1))) * 16),2,'0')||'-'||
LPAD(TO_CHAR(TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),6,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),6,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),5,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),5,1))) * 16),2,'0')||'-'||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),2,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),2,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),1,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),1,1))) * 16) - 100),2,'0')||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),4,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),4,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),3,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),3,1))) * 16) - 100),2,'0')||' '||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),10,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),10,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),9,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),9,1))) * 16) - 1),2,'0')||':'||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),12,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),12,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),11,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),11,1))) * 16) - 1),2,'0')||':'||
LPAD(TO_CHAR((TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),14,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),14,1))) +
TO_NUMBER(DECODE(SUBSTR(rawtohex(t.high_value),13,1),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),13,1))) * 16) - 1),2,'0')
,'NUMBER'
,DECODE(SIGN(NVL(LENGTH(rawtohex(t.high_value)),0) - 3),-1,''
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),4,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),4,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),3,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),3,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.high_value)),0) - 5),-1,''
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),6,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),6,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),5,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),5,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.high_value)),0) - 7),-1,''
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),8,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),8,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),7,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),7,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.high_value)),0) - 9),-1,''
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),10,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),10,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),9,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),9,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.high_value)),0) - 11),-1,''
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),12,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),12,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),11,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),11,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.high_value)),0) - 13),-1,''
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),14,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),14,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),13,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),13,1))) * 16) - 1)
),2,'0'))||
DECODE(SIGN(NVL(LENGTH(rawtohex(t.high_value)),0) - 15),-1,''
,LPAD(TO_CHAR(((TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),16,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),16,1))) +
TO_NUMBER(DECODE(NVL(SUBSTR(rawtohex(t.high_value),15,1),'0'),'A','10','B','11','C','12','D','13','E','14','F','15'
,SUBSTR(rawtohex(t.high_value),15,1))) * 16) - 1)
),2,'0'))
,'N/A'
)) high_value
, t.data_default
FROM dba_tab_cols t
, dba_tab_col_statistics tcs
WHERE UPPER(t.table_name) = UPPER('&Table_name')
AND t.owner = UPPER(NVL('&Owner',USER))
AND t.table_name = tcs.table_name(+)
AND t.column_name = tcs.column_name(+)
AND t.owner = tcs.owner(+)
/