Creating my own Data dictionary

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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"?


Re: Creating my own Data dictionary

Quoted text here. Click to load it

You might be able to do this by creating a view on information_schema.

Quoted text here. Click to load it

select COLUMN_NAME from information_schema.COLUMNS
where TABLE_SCHEMA='database name' and TABLE_NAME='Vehicles';

You can also use "describe Vehicles;" in current and older versions of
MySQL, but the format isn't the same.

                        Gordon L. Burditt

Re: Creating my own Data dictionary

On Sat, 22 Apr 2006 01:48:23 GMT, in mailing.database.mysql Matt

Quoted text here. Click to load it

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
$AppDatabase = "xxxxxxx";        //--- your database
$TblArr = array();

//--- get table names from AppDatabase db
$sql = "SHOW TABLES FROM ".$AppDatabase;
$result = mysql_query( $sql );
while ($row = mysql_fetch_row($result))
    $TblArr[$ctr++] = $row[0];


//--- 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`)
$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
    $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
    $res2 = mysql_query( $sql2 );
    $ct = 1;
    while ($i = mysql_fetch_array($result))
        $sql2 = "INSERT INTO ".$AppDatabase."_tbldefs
        VALUES ('".$TblArr[$row]."',".$ct.",\"".$i[0].
        $result2 = mysql_query( $sql2 );
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

Site Timeline