#!/usr/bin/perl use strict; #====================================================================== # Copyright: (c) 2000 Hooker # Author: hooker # Name: ShowDBStructure # Parameters: # None # Description: # This connects to a database and builds an HTML page which holds the # description of each of the tables. # # If you're using MySQL's internal permissions database (highly recommended), # the user that you give here only needs select access. Everything ought to # be self explanatory. # Maintenance: # 0.1 200003xx hooker Initial release version #====================================================================== use DBI; use Mysql; my $Version = '0.1'; # # Obviously these variables get changed to the right database, user and # password. # my $Database = 'xxx'; my $DBUser = 'username'; my $DBPassword = 'password'; my $dbh = DBI -> connect ('dbi:mysql:' . $Database, $DBUser, $DBPassword, '') || die "Connect to $Database failed"; my $rc; #---------------------------------------------------------------------- my $PageBackground = "white"; print < Data Structure

Database Structure

EOM_1 my $sth = $dbh -> prepare (q{show tables}); $rc = $sth -> execute (); while ( my ($TableName) = $sth -> fetchrow_array () ) { my $Cmd = "select count(*) from $TableName"; my $sthCmd = $dbh -> prepare ($Cmd); $rc = $sthCmd -> execute (); my ($N) = $sthCmd -> fetchrow_array (); print <

Structure of $TableName ( $N rows )

EOM_2 $Cmd = "describe $TableName"; $sthCmd = $dbh -> prepare ($Cmd); $rc = $sthCmd -> execute (); while ( my ($Field, $Type, $Null, $Key, $Default, $Extra) = $sthCmd -> fetchrow_array () ) { $Null = ' ' unless ($Null); $Key = ' ' unless ($Key); $Default = ' ' unless ($Default); $Extra = ' ' unless ($Extra); print "\n\t\n\t\n\t\n", "\t\n\t\n\t\n\n"; } print "
Field Type Null Key Default Extra
$Field $Type $Null $Key $Default $Extra
\n"; } #---------------------------------------------------------------------- print <

Back
EOM_LAST $rc = $sth -> finish (); $rc = $dbh -> disconnect (); exit (0);