How to fetch the names of tables present in database-CodePudding

I have a piece of code where I wanted to fetch the database information and store the required data, I am already connected to the database what I am looking for is to fetch the name of the tables present inside the database so that I can access them one by one and fetch the required information.

Here's my code

#!/usr/bin/perl
use DBI;
use strict;

use warnings;
use Switch;
use Data::Dumper;
use Spreadsheet::Read qw(ReadData);
my   @expValues ="";
my $portDes="";
my $portValue="";
my $ttidDes;
my $ttidValue;
my $tidvDes;
my $tidvValue;
my $tiDes;
my $tiValue;
my $redirectHostDes;
my $redirectHostValue;
my $saQueryDes;
my $saQueryValue;
my $dialedNumDes;
my $dialedNumValue;
my $dbType = "mysql"; 
my $database = "control";
my $host="localhost";
my $dsn="";
my $userid="";
my $password="";
if($dbType eq "mysql"){
$dsn = "DBI:$dbType:database=$database;host=$host:port=XXXX";
$userid = "XXXX";
$password = 'XXXX';
}else{
$dbType="postgres";
$userid = "postgres";
$password = "postgres";
$dsn = "DBI:$dbType:database=$database;host=$host,port=XXXX";
}

my $dbcon = DBI->connect($dsn, $userid, $password) or die "Can't connect to database: $DBI::errstr\n";
print "connected to the database\n";


my $notables = $dbcon->tables();
print "No of tables : $notables" ;

sub getXlxsDetails(){
my $book = ReadData ($_[0]);
my $configrows = $_[1];
#print $configrows;
my @rows;
#my @port;
#my @ttid;
#my @ti;
#my @tidv;
#my @redirect;
#my @saQuery;
#my @dialed;
#my @returnInfo;
if($configrows eq ""){
    $portValue="Null";
    $ttidValue="Null";
    $tidvValue="Null";
    $tiValue="Null";
    $redirectHostValue="Null";
    $saQueryValue="Null";
    $dialedNumValue="Null";
    print ("NOT NULL**************************");
    return $portValue,$ttidValue,$tidvValue,$tiValue,$redirectHostValue,$saQueryValue,$dialedNumValue;
}
else{
if($configrows eq 'Development'){
@rows = Spreadsheet::Read::rows($book->[1]);
}
if($configrows eq 'Production'){
@rows = Spreadsheet::Read::rows($book->[2]);       
}
foreach my $i (1 .. scalar @rows) {
    foreach my $j (1 .. scalar @{$rows[$i-1]}) {
        my $key = $rows[$i-1][$j-1] // '';
        switch($key){
        case "Port Assigned " {
        print ("MATCHED************************** Port Assigned\n");
        $portDes = (($rows[$i-1][$j-1])."\n");
        $portValue = (($rows[$i-1][($j 1)-1])."\n");
        #@returnInfo[$j] = ($portValue);
        }
        case "TTID (Y/N, default: Y)" {
        print ("MATCHED************************** TTID (Y/N, default: Y)\n");
        $ttidDes= (($rows[$i-1][$j-1])."\n");
        $ttidValue=(($rows[$i-1][($j 1)-1])."\n");
        #@returnInfo[$j 1] = ($ttidValue);
       }
       case "TIDV (Y/N, default: Y)" {
        print ("MATCHED************************** TIDV (Y/N, default: Y)\n");
        $tidvDes=(($rows[$i-1][$j-1])."\n");
        $tidvValue= (($rows[$i-1][($j 1)-1])."\n");
        #@returnInfo[$j 2] = ($tidvValue);
        }
        case "TI (Y/N, default: Y)" {
        print ("MATCHED************************** TI (Y/N, default: Y)\n");
        $tiDes= (($rows[$i-1][$j-1])."\n");
        $tiValue=(($rows[$i-1][($j 1)-1])."\n");
        #@returnInfo[$j 3] = ($tiValue);
        }
        case "Redirect host FQDN (default)" {
        print ("MATCHED************************** Redirect host FQDN (default)\n");
        $redirectHostDes=(($rows[$i-1][$j-1])."\n");
        $redirectHostValue=(($rows[$i-1][($j 1)-1])."\n");
        #@returnInfo[$j 4] = ($redirectHostValue);
        }
        case "SA-Query URL" {
        print ("MATCHED************************** SA-Query URL\n");
        $saQueryDes=(($rows[$i-1][$j-1])."\n");
        $saQueryValue=(($rows[$i-1][($j 1)-1])."\n");
        #@returnInfo[$j 5] = ($saQueryValue);
        }
    case "Dialed Number (from SIP invite)" {
        print ("MATCHED************************** Dialed Number (from SIP invite)\n");
        $dialedNumDes= ($rows[$i-1][$j-1]);
        $dialedNumValue=($rows[($i   1)-1][$j-1]);
        #@returnInfo[$j 6] = ($dialedNumValue);
        }

   }
        next;
}
}
return $portValue,$ttidValue,$tidvValue,$tiValue,$redirectHostValue,$saQueryValue,$dialedNumValue;
}


#@returnInfo = (\@port,\@ttid,\@ti,\@tidv,\@redirect,\@saQuery,\@dialed);


}
#my $Result=&getXlxsDetails(\@rowsDev,"Port Assigned ");
my ($result1,$result2,$result3,$result4,$result5,$result6,$result7)=&getXlxsDetails('024_Goldman_Sachs.xlsx','Development');
print($result1."\n".$result2."\n".$result3."\n".$result4."\n".$result5."\n".$result6."\n".$result7);
#print "@$result\n";

print "\n";

The output I am getting is:

(tc-lab-04) /home/trustid/switchadaptor/testauto/dataverificationtesting2/XL_Parsing>./aa.pl
**connected to the database**
No of tables : 1MATCHED************************** Port Assigned
MATCHED************************** SA-Query URL
MATCHED************************** TTID (Y/N, default: Y)
MATCHED************************** TIDV (Y/N, default: Y)
MATCHED************************** TI (Y/N, default: Y)
MATCHED************************** Redirect host FQDN (default)
MATCHED************************** Dialed Number (from SIP invite)
10272

Y

Y

N

ivr.localdomain

https://devapi-primary.trustidinc.com/tid

8332259639

--> It is giving me no. of table, instead of no. of tables I wanted to fetch the name of all the tables present in the database.

Code snippets I already used:

my @names = $dbcon->tables( $catalog, $schema, $table, $type );
print Dumper @names;

I am working on the Virtual Machine, basically it is my office lab server & the DB is deployed in this server.

CodePudding user response：

Using $dbcon->tables() as the question does is a good way indeed, but that returns the list of tables while it is assigned to a scalar -- so you get the number of tables, as observed.^† Also, calling it without parameters is deprecated (see docs linked below). A correct way to use this is shown in the answer by Dave Sherohman.

Or, use table_info (that tables is a "simple interface to"), to obtain and review more detailed information than just the names, and see which of the tables are suitable

my $sth = $dbh->table_info('', '', '%', 'TABLE');
my $res = $sth->fetchall_arrayref;
print Dumper $res;

or with

my $sth = $dbh->table_info('', '', '', 'TABLE');

or even

my $sth = $dbh->table_info(undef, undef, undef, 'TABLE');

If you leave out that last 'TABLE' (and use '' or undef instead) then the VIEWs will be included in the resutls as well, what may well be of interest.

^† The question also shows the correct

my @names = $dbcon->tables( $catalog, $schema, $table, $type );

but it doesn't tell us what happens and what all those variables are

CodePudding user response：

The previous comments and answer referring to INFORMATION_SCHEMA should be noted as only applicable to MySQL/MariaDB. Which, admittedly, is the database that your DBI->connect statement indicates you're using, but there is also a database-engine-independent way to do it:

my @tables = $dbcon->tables(undef, undef, undef, 'TABLE');

CodePudding user response：

how to get the name of the tables present in unknown database

You may retrieve the names of the databases which are present on the server (including system databases) with

SELECT SCHEMA_NAME AS `Database`
FROM INFORMATION_SCHEMA.SCHEMATA;

You may retrieve the names of the tables in definite database with

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'db_name';

or in current database with

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = DATABASE();