Repository navigation
Expand file tree
/
Copy pathcons.sql
More file actions
171 lines (126 loc) · 5.09 KB
/
Copy pathcons.sql
File metadata and controls
171 lines (126 loc) · 5.09 KB
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
declare
-- Show information about a constraint.
--
-- Parameter: name of constraint
--
-- Currently: only PK, FK, unique and check constraints supported.
--- NOT NULL constraints not yet supported.
r_constraint dba_constraints%rowtype;
r_constraint_pk dba_constraints%rowtype;
r_index dba_indexes%rowtype;
owner_ varchar2(30);
constraint_name_ varchar2(30);
dot_pos number;
begin
dbms_output.new_line;
dot_pos := instr('&1', '.');
if dot_pos > 0 then
owner_ := upper(substr('&1', 1, dot_pos-1));
constraint_name_ := upper(substr('&1', dot_pos + 1 ));
else
constraint_name_ := upper('&1');
end if;
dbms_output.put_line('Owner: ' || owner_);
dbms_output.put_line('constraint name ' || constraint_name_ );
begin
select * into r_constraint from dba_constraints
where
constraint_name = constraint_name_ and
nvl(owner_, owner) = owner;
dbms_output.put_line('type: ' || r_constraint.constraint_type);
exception when no_data_found then
-- Maybe it's a unique constraint in an index-disguise...
select * into r_index from dba_indexes
where
index_name = constraint_name_ and
nvl(owner_, owner) = owner and
uniqueness ='UNIQUE';
dbms_output.put_line(' Unique key constraint [defined as index]');
dbms_output.new_line;
dbms_output.put_line(' Table: ' || r_index.table_name);
dbms_output.new_line;
for cols in (
select column_name
from dba_ind_columns
where
index_name = constraint_name_ and
nvl(owner_, index_owner) = index_owner
order by column_position
) loop
dbms_output.put_line(' ' || cols.column_name);
end loop;
end;
if r_constraint.constraint_type = 'R' then -- {
select * into r_constraint_pk from dba_constraints
where
constraint_name = r_constraint.r_constraint_name and
owner = r_constraint.r_owner;
dbms_output.put_line(' Foreign Key constraint');
dbms_output.new_line;
dbms_output.put_line(' ' || rpad(r_constraint.owner , 30) || ' ' || r_constraint_pk.owner);
dbms_output.put_line(' ' || rpad(r_constraint.table_name, 30) || ' -> ' || r_constraint_pk.table_name);
dbms_output.put_line(' ' || rpad('-', 30, '-') || ' ' || rpad('-', 30, '-'));
for cols in (
select
fk_col.column_name column_name_fk,
pk_col.column_name column_name_pk
from
dba_cons_columns fk_col,
dba_cons_columns pk_col
where
fk_col.constraint_name = r_constraint .constraint_name and
pk_col.constraint_name = r_constraint_pk.constraint_name and
fk_col.position = pk_col.position
order by
fk_col.position
) loop
dbms_output.put_line(' ' || rpad(cols.column_name_fk, 30) || ' ' || cols.column_name_pk);
end loop;
-- }
elsif r_constraint.constraint_type in ('P', 'U') then -- {
if r_constraint.constraint_type = 'P' then
dbms_output.put_line(' Primary Key constraint');
else
dbms_output.put_line(' Unique key constraint');
end if;
dbms_output.new_line;
dbms_output.put_line(' Table: ' || r_constraint.table_name);
dbms_output.new_line;
for cols in (
select column_name
from dba_cons_columns
where
constraint_name = r_constraint.constraint_name and
nvl(owner_, owner) = owner
order by position
) loop
dbms_output.put_line(' ' || cols.column_name);
end loop;
-- }
elsif r_constraint.constraint_type = 'C' then -- {
dbms_output.put_line(' Check constraint');
dbms_output.new_line;
dbms_output.put_line(' Condition: ' || r_constraint.search_condition);
dbms_output.new_line;
dbms_output.put_line(' Table: ' || r_constraint.table_name);
dbms_output.put (' Columns: ');
for cols in (
select /* position, */ -- Seems to be null for check constraints...
row_number() over (order by position) row_,
column_name
from dba_cons_columns
where
constraint_name = r_constraint.constraint_name and
owner = r_constraint.owner
order by position
) loop
if cols.row_ = 1 then
dbms_output.put_line(cols.column_name);
else
dbms_output.put_line(' ' || cols.column_name);
end if;
end loop;
dbms_output.new_line;
end if; -- }
end;
/