[MySql] dumping table structure

Sometimes it is useful to be able to take a quick look on the table structure of a database your working with. Most of the time I use phpMyAdmin for this. Additionally I wanted a small script that I can quickly use in my terminal.

#!/usr/bin/perl

use strict;
use warnings;

use DBI;
use Data::Dumper;

my $table = shift @ARGV || 'people';

my $dbh = DBI->connect("DBI:mysql:database=experts;host=localhost",
                       "developer", "secret", {'RaiseError' => 1});

my $sth = $dbh->prepare("SELECT * FROM $table");
die "Error: " . $dbh->errstr . "\n" unless ($sth);
die "Error: " . $sth->errstr . "\n" unless ($sth->execute);

my $names         = $sth->{'NAME'};
my $type          = $sth->{'mysql_type_name'};
my $length        = $sth->{'mysql_length'};
my $is_nullable   = $sth->{'NULLABLE'};
my $is_pri_key    = $sth->{'mysql_is_pri_key'};
my $is_autoinc    = $sth->{'mysql_is_auto_increment'};

$sth->finish();
$dbh->disconnect();

my @structure = map {
   { 
      f_name      => $names->[$_],
      f_type      => $type->[$_],
      f_length    => $length->[$_], 
      f_nullable  => $is_nullable->[$_] ? 1 : 0,
      f_pri_key   => $is_pri_key->[$_]  ? 1 : 0,
      f_autoinc   => $is_autoinc->[$_]  ? 1 : 0,
   };
} (0..$#{$names});

die Dumper \@structure;
[download]

Comment on [MySql] dumping table structure Download Code

Replies are listed 'Best First'.
Re: [MySql] dumping table structure by b10m (Vicar) on Jun 17, 2005 at 11:13 UTC
I'd just use: `$ mysql -u <user> -p -e "DESCRIBE <table>;" <database>` [download] IMHO, that provides even a cleaner/more readable result. -- b10m All code is usually tested, but rarely trusted.	[reply] [d/l]
Re^2: [MySql] dumping table structure by neniro (Priest) on Jun 17, 2005 at 11:43 UTC
You're right, it looks better. To my shame I have to admit that I haven't used the mysql-cli often until now. I use the @structure dumped by my snippet in my app, so that it is still usefull to me, but your solution is better in general. Thanks you, neniro	[reply]
Re: [MySql] dumping table structure by jch341277 (Sexton) on Aug 05, 2005 at 20:28 UTC
You can also use: `$ mysqldump -d dbname tablename` The "-d" prevents the client from providing all the row data... eg: `$ mysqldump -d qosa xlat # MySQL dump 8.12 # # Host: localhost Database: qosa #-------------------------------------------------------- # Server version 3.23.33-log # # Table structure for table 'xlat' # CREATE TABLE xlat ( id int(11) NOT NULL default '0', rexpr text, repla text, PRIMARY KEY (id) ) TYPE=MyISAM;` [download]	[reply] [d/l] [select]
Re: [MySql] dumping table structure by trammell (Priest) on Aug 05, 2005 at 21:00 UTC
In the interests of completeness: `% echo "show create table mytable" \| mysql -r mydb` [download] I prefer the "create table" syntax because table index information is complete, unlike the output of DESCRIBE table.	[reply] [d/l]
Re^2: [MySql] dumping table structure by thor (Priest) on Aug 05, 2005 at 21:11 UTC
I know nothing of MySQL, but do indicies have to be created at table create time? In other database engines, you can create them whenever. If that's the case in MySQL, will your command necessarily show the indicies? Just curious... thor `Feel the white light, the light within Be your own disciple, fan the sparks of will For all of us waiting, your kingdom will come`	[reply]
Re^3: [MySql] dumping table structure by lupey (Monk) on Aug 12, 2005 at 20:10 UTC
"I know nothing of MySQL, but do indicies have to be created at table create time?" No, you can add them with the ALTER TABLE syntax. See the MYSQL docs at ALTER Table syntax lupey	[reply]


No such thing as a small change
	PerlMonks