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
| 6 Useful Oracle Data Dictionary Queries Every DBA Should Have - Dataedo Blog
Columns in a table – I had to do this because the oracle sql developer was not giving me any data.
select col.owner as schema_name,
col.table_name,
col.column_name,
col.data_type,
decode(char_length,
0, data_type,
data_type || '(' || char_length || ')') as data_type_ext,
col.data_length,
col.data_precision,
col.data_scale,
col.nullable,
col.data_default as default_value,
nvl(pk.primary_key, ' ') as primary_key,
nvl(fk.foreign_key, ' ') as foreign_key,
nvl(uk.unique_key, ' ') as unique_key,
nvl(check_const.check_constraint, ' ') check_constraint,
comm.comments
from all_tables tab
inner join all_tab_columns col
on col.owner = tab.owner
and col.table_name = tab.table_name
left join all_col_comments comm
on col.owner = comm.owner
and col.table_name = comm.table_name
and col.column_name = comm.column_name
left join (select constr.owner,
col_const.table_name,
col_const.column_name,
'PK' primary_key
from all_constraints constr
inner join all_cons_columns col_const
on constr.constraint_name = col_const.constraint_name
and col_const.owner = constr.owner
where constr.constraint_type = 'P') pk
on col.table_name = pk.table_name
and col.column_name = pk.column_name
and col.owner = pk.owner
left join (select constr.owner,
col_const.table_name,
col_const.column_name,
'FK' foreign_key
from all_constraints constr
inner join all_cons_columns col_const
on constr.constraint_name = col_const.constraint_name
and col_const.owner = constr.owner
where constr.constraint_type = 'R'
group by constr.owner,
col_const.table_name,
col_const.column_name) fk
on col.table_name = fk.table_name
and col.column_name = fk.column_name
and col.owner = fk.owner
left join (select constr.owner,
col_const.table_name,
col_const.column_name,
'UK' unique_key
from all_constraints constr
inner join all_cons_columns col_const
on constr.constraint_name = col_const.constraint_name
and constr.owner = col_const.owner
where constr.constraint_type = 'U'
union
select ind.owner,
col_ind.table_name,
col_ind.column_name,
'UK' unique_key
from all_indexes ind
inner join all_ind_columns col_ind
on ind.index_name = col_ind.index_name
where ind.uniqueness = 'UNIQUE') uk
on col.table_name = uk.table_name
and col.column_name = uk.column_name
and col.owner = uk.owner
left join (select constr.owner,
col_const.table_name,
col_const.column_name,
'Check' check_constraint
from all_constraints constr
inner join all_cons_columns col_const
on constr.constraint_name = col_const.constraint_name
and col_const.owner = constr.owner
where constr.constraint_type = 'C'
group by constr.owner,
col_const.table_name,
col_const.column_name) check_const
on col.table_name = check_const.table_name
and col.column_name = check_const.column_name
and col.owner = check_const.owner
where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
'LBACSYS', 'MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS',
'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM',
'TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000',
'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES','MDDATA',
'ORACLE_OCM','SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
'XS$NULL','PUBLIC')
order by col.owner,
col.table_name,
col.column_name;
|