A fellow in our shop wrote a perl script to do just that.
Enjoy!
Andy
Andy Ingham
Systems Librarian
Academic Affairs Library
UNC-Chapel Hill
919-962-1288
[EMAIL PROTECTED]
*********************************************************************************************************
#!/usr/local/bin/perl
# describe.pl
#
# Tim Shearer
#
# Academic Affairs Library
# University of North Carolia at Chapel Hill
# [EMAIL PROTECTED]
# 01/28/2002
#
# Please use, distribute, and tweak freely. I would appreciate
# my name staying with the distributed versions.
#
# This perl script will do a describe for all tables in a mysql database
# and save the output as a text file locally.
#
# It requires that you have perl and the perl DBI library installed.
# Note that you may need to change the location of perl (above) to
reflect
# where it lives on your box.
#
# The script relies on a .my.cnf file for usernames/passwords. It could
# be tweaked to let a user input mysql usernames and passwords
dynamically.
#
# If the .my.cnf user is root, it offers to allow a describe on all
databases.
# If not, the user must input the name of a database where they have
appropriate
# permissions for a describe.
#
# The script does a describe on each table, does some formatting (which
is
# not very sophisticated, long lines won't look pretty), and outputs the
results
# to the users local directory (from which they are running the script)
# in a file with this pattern: databasename.dsc
#
# Naturally, it can be run locally, or put out for anyone on a server to
use.
# Don't forget to make it executable if you're not familiar with
scripting...
use DBI;
$homedir = $ENV{HOME};
$input = $homedir.'/.my.cnf';
open (INFILE, "<$input") || die "Can't open hidden files\n";
while (<INFILE>){
chomp;
@pair = split("=", $_);
$mycnf{$pair[0]} = $pair[1];
}
my $username = $mycnf{'user'};
my $hostname = $mycnf{'host'};
my $password = $mycnf{'password'};
if ($username eq "root"){ # if root, give them a list of dbs
my $data_source = "DBI:mysql:mysql";
my $dbh = DBI->connect( $data_source, $username, $password );
if ( !defined $dbh ){
die "Cannot do \$dbh->connect: $DBI::errstr";
}
my $query = qq(show databases);
my $select_sql = qq{ $query };
my $select_sth = $dbh->prepare( $select_sql );
$select_sth->execute() or die "Unable to execute query: $dbh->errstr";
while(@ary = $select_sth->fetchrow() ){
$db = $ary[0];
unless($db eq 'mysql' || $db eq 'lost+found'){
push (@db, $db)
}
}
$select_sth->finish();
$dbh->disconnect;
$count = 1;
foreach $database (@db){
print "$count - $database\n";
$count++;
}
print "Choose a number from above: ";
$chosen = <STDIN>;
chomp($chosen);
unless($chosen eq "mysql"){
$chosen--;
$chosen = @db[$chosen];
}
}else{ # they're not root, let them choose one by typing it directly in
print "Please input a database that you have access to: ";
$chosen = <STDIN>;
chomp($chosen);
}
my $data_source = "DBI:mysql:$chosen:$hostname";
my $dbh = DBI->connect( $data_source, $username, $password ) || die
"\n\nYou do not have permission to access this database.\n\n\n";
if ( !defined $dbh ){
die "Cannot do \$dbh->connect: $DBI::errstr";
}
open(OUTFILE, ">$chosen.dsc");
print OUTFILE "$chosen\n";
# Set up a query and prepare it...
my $query = qq(show tables);
my $select_sql = qq{ $query };
my $select_sth = $dbh->prepare( $select_sql );
$select_sth->execute() or die "Unable to execute query: $dbh->errstr";
while(@ary = $select_sth->fetchrow() ){
$table = $ary[0];
push (@tables, $table);
}
$select_sth->finish();
foreach $tb (@tables){
print OUTFILE "\n$tb\n";
my $query = qq(describe $tb);
my $select_sql = qq{ $query };
my $select_sth = $dbh->prepare( $select_sql );
$select_sth->execute() or die "Unable to execute query: $dbh->errstr";
print OUTFILE
'+------------------+---------------------------------+------+-----+------------+----------------+'."\n";
print OUTFILE '| Field | Type |
Null | Key | Default | Extra |'."\n";
print OUTFILE
'+------------------+---------------------------------+------+-----+------------+----------------+'."\n";
while(my ($fld, $type, $nul, $key, $dft, $xtra) =
$select_sth->fetchrow() ){
printf OUTFILE "|%-18s|%-33s|%-6s|%-5s|%-12s|%-16s|\n", $fld, $type,
$nul, $key, $dft, $xtra;
}
print OUTFILE
'+------------------+---------------------------------+------+-----+------------+----------------+'."\n";
$select_sth->finish();
}
$dbh->disconnect;
exit;
*********************************************************************************************************
--- Original message ----------
Subject: describe all tables
Date: Sun, 27 Jan 2002 18:52:04 -0800
From: Michael Collins <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Can "describe" be used to show columns from all tables in a database?
All at once that is. I want to document all fields in all tables and
find I need to display one table at a time.
--
Michael
__
||| Michael Collins |||
||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED]
||| Seattle, WA, USA ||| http://www.lassodev.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
