On Sat, 22 Apr 2006 01:48:23 GMT, in mailing.database.mysql Matt
<ma*************@yahoo.com>
<sm********************************@4ax.com> wrote:
| Stop me if you've heard this one...
|
| I want to create a "data dictionary" table of all my user tables in a
| certain database. The data dictionary (my version) will contain
| columns such as "TableName", "ColumnName", "DataType", "DataLength",
| etc, etc. I know this information is available in the MySQL engine
| tables, I just don't know where to look for it. I'm using 5.0.
|
| Any hints? For example, how do I obtain a list of all the ColumnNames
| and data types in a table I created named "Vehicles"?
|
| Thanks!!
| Matt
I'm using 4.0.19 so there might be better ways for 5.0 version.
Also, I'm a complete novice at php so the code might be better
optimised.
----------------------------------------------------------------
$AppDatabase = "xxxxxxx"; //--- your database
$TblArr = array();
//--- get table names from AppDatabase db
$sql = "SHOW TABLES FROM ".$AppDatabase;
$result = mysql_query( $sql );
$ctr=1;
while ($row = mysql_fetch_row($result))
$TblArr[$ctr++] = $row[0];
mysql_free_result($result);
//--- create the table to hold the definitions
$sql = "CREATE TABLE ".$AppDatabase."_tbldefs (
`id` int(10) unsigned NOT NULL auto_increment,
`TableName` varchar(255) default '',
`OrderNbr` int(10) unsigned default '0',
`FieldName` varchar(255) default '',
`DataType` varchar(255) default '',
`AllowNull` varchar(255) default '',
`isKey` varchar(255) default '',
`DefaultValue` varchar(255) default '',
`Extras` varchar(255) default '',
`FieldComments` varchar(255) default '',
`Indices` text,
`TblComment` varchar(255) default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM";
$result = mysql_query( $sql );
//--- build tblDefs info
for ( $row = 1; $row < sizeof($TblArr); $row++ )
{
$sql = "show columns from ".$AppDatabase.".".$TblArr[$row];
$result = mysql_query( $sql );
//--- get the table comment
$sql2 = "SHOW table STATUS FROM ".$AppDatabase." like
'".$TblArr[$row]."'";
$res2 = mysql_query( $sql2 );
$i = mysql_fetch_array($res2);
$tblComment = $i["Comment"];
//--- get the indices for the table
$sql2 = "SHOW INDEX FROM ".$AppDatabase.".".$TblArr[$row];
$res2 = mysql_query( $sql2 );
$Indices = "";
while($i = mysql_fetch_array($res2) )
{
if( $Indices != "" ) $Indices .= "<br />";
$Indices .= $i["Column_name"]." => ".$i["Key_name"];
}
//--- now store into database as first record for this table def
$sql2 = "INSERT INTO ".$AppDatabase."_tbldefs
(TableName,OrderNbr,Indices,TblComment) VALUES
('".$TblArr[$row]."',0,\"".$Indices."\",\"".$tblComment."\")";
$res2 = mysql_query( $sql2 );
$ct = 1;
while ($i = mysql_fetch_array($result))
{
$sql2 = "INSERT INTO ".$AppDatabase."_tbldefs
(TableName,OrderNbr,FieldName,DataType,AllowNull,i sKey,DefaultValue,Extras)
VALUES ('".$TblArr[$row]."',".$ct.",\"".$i[0].
"\",\"".$i[1].
"\",\"".$i[2].
"\",\"".$i[3].
"\",\"".$i[4].
"\",\"".$i[5].
"\")";
$result2 = mysql_query( $sql2 );
$ct++;
}
}
mysql_free_result($result);
----------------------------------------------------
HTH
---------------------------------------------------------------
I often wish that email had never been invented, but there’s
just no way I can get rid of it. So, day after day, several times
a day, I dutifully delete 99% of the emails I receive, and when
I’m not able to get at my email for a few days, I’ll leave the
machine at home running to pick it up every 10 minutes so I don’t
overflow some capacity somewhere, and just the other day I caught
myself wondering who will clean out my Inbox after I’m dead.
Charles Petzold. October 20, 2005
---------------------------------------------------------------
