Creating my own Data dictionary

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

Re: Creating my own Data dictionary

>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.

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

>Any hints? For example, how do I obtain a list of all the ColumnNames
>and data types in a table I created named "Vehicles"?

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

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 .= "
";
$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,isKey,Defau ltValue,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
------------------------------------------------------------ ---