-
Notifications
You must be signed in to change notification settings - Fork 0
/
parent_and_child_tables.pl
68 lines (55 loc) · 1.8 KB
/
parent_and_child_tables.pl
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
#
# parent_and_child_tables.pl TABLENAME USERNAME PASSWORD DATABASE
#
# Show primary key, dependent child tables and referenced parent
# tables for TABLENAME.
#
use warnings;
use strict;
use DBI;
my $table_name = shift;
my $username = shift;
my $password = shift;
my $database = shift;
my %attr = ();
my $dbh = DBI->connect("dbi:ODBC:$database", $username, $password, \%attr) or die;
print "\nPrimary Key\n";
print " ", $dbh -> primary_key('', '', $table_name), "\n";
print "\nChild Tables\n";
my $res_fk = $dbh->selectall_arrayref("exec sp_fkeys \@pktable_name='$table_name'");
for my $r (@$res_fk) {
printf(" %-40s | %-20s | %2d | %-60s | %-50s\n",
# $$r[ 0], # PKTABLE_QUALIFIER
# $$r[ 1], # PKTABLE_OWNER
# $$r[ 2], # PKTABLE_NAME
# $$r[ 3], # PKCOLUMN_NAME
# $$r[ 4], # FKTABLE_QUALIFIER
# $$r[ 5], # FKTABLE_OWNER
$$r[ 6], # FKTABLE_NAME
$$r[ 7], # FKCOLUMN_NAME
$$r[ 8], # KEY_SEQ
# $$r[ 9], # UPDATE_RULE [0=Cascade, 1=No action]
# $$r[10], # DELETE_RULE [0=Cascade, 1=No action]
$$r[11], # FK_NAME
$$r[12], # PK_NAME
)
}
print "\nParent Tables\n";
$res_fk = $dbh->selectall_arrayref("exec sp_fkeys \@fktable_name='$table_name'");
for my $r (@$res_fk) {
printf(" %-40s | %-20s | %2d | %-60s | %-50s\n",
# $$r[ 0], # PKTABLE_QUALIFIER
# $$r[ 1], # PKTABLE_OWNER
$$r[ 2], # PKTABLE_NAME
# $$r[ 3], # PKCOLUMN_NAME
# $$r[ 4], # FKTABLE_QUALIFIER
# $$r[ 5], # FKTABLE_OWNER
# $$r[ 6], # FKTABLE_NAME
$$r[ 7], # FKCOLUMN_NAME
$$r[ 8], # KEY_SEQ
# $$r[ 9], # UPDATE_RULE [0=Cascade, 1=No action]
# $$r[10], # DELETE_RULE [0=Cascade, 1=No action]
$$r[11], # FK_NAME
$$r[12], # PK_NAME
)
}