Get Primary Keys of All User Tables in Oracle

"You can never cross the ocean until you have the
courage to lose sight of the shore"

--Christopher Columbus

We can get the primary keys and other constraint information of all the user tables with the following query:

SELECT cols.table_name, cols.column_name
FROM user_constraints cons, user_cons_columns cols
WHERE cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY 1;

       

The above will not show the tables which does not have a primary key. We can use the following query for finding that:

SELECT table_name FROM user_tables 
WHERE table_name NOT IN
(
  SELECT DISTINCT table_name 
  FROM user_constraints 
  WHERE constraint_type = 'P'
)
ORDER BY 1;

Thanks to this post for this above query.

Following is a perl script which will print all the primary keys and also the tables which does not have a one.

use DBI;

my $tns1="pearl11g";
my $dbauser1="plsql";
my $dbapass1="oracle";

my $dbh1=DBI->connect("dbi:Oracle:$tns1" , $dbauser1 , $dbapass1) || die "db connection fail to $tns1 \n";

#First get all the user's tables
my @tables = &getAllTables($dbh1);
#print "tables: @tables\n"; #UnComment this line if you want to see all the tables
print "\n========================================================================\n";
printf "%-25s| %-22s |%-25s\n", "Table", "Columnname", "PK-ConsName";
print "------------------------------------------------------------------------\n";

#Iterate over each table to see if PK is available, print if none exists else print the PK information
foreach (@tables) {
 my $table = $_;
 my @info = &getPKeys($dbh1, $table);
 if (!@info) {
  print "  *** Table $table doesnot have a PrimaryKey....***\n";
 }
 foreach (@info) {
 printf "%-25.25s| %-22.22s |%-25.25s\n","@$_[0]","@$_[1]","@$_[2]";
 }
}
#Get all the user's tables
sub getAllTables {
 my $db = $_[0];
 my @rows;
 
 my $sqlstr = "SELECT table_name FROM user_tables ORDER BY 1";
 my $sth=$db->prepare($sqlstr);
 $sth->execute;
 while (my $row=$sth->fetchrow_array() ) {
 push @rows, $row;
 }
 $sth->finish;
return @rows;
}

#Get the Primary keys of each table, returns empty if none exists
sub getPKeys {
 my $db = $_[0];
 my $tname = $_[1];
 my @rows;
 my $row;
 
 my $sqlstr = "SELECT cols.table_name, cols.column_name, cons.constraint_name, cons.constraint_type, cons.status ".
 " FROM user_constraints cons, user_cons_columns cols ".
 " WHERE cons.constraint_type = 'P' ".
 " AND cols.table_name = '$tname' ".
 " AND cons.constraint_name = cols.constraint_name ".
 " AND cons.owner = cols.owner ";

 my $sth=$db->prepare($sqlstr);
 $sth->execute;
 while ($row=$sth->fetchrow_arrayref()) {
  push @rows, [@$row];
 }
 $sth->finish;
 return @rows;
}

Edit the tns and DB username/password and save the program as "getPK.pl".
Sample output as below: