469,609 Members | 1,666 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

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

Apr 22 '06 #1
2 4446
>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
Apr 22 '06 #2
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
---------------------------------------------------------------
Apr 22 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by ben moretti | last post: by
4 posts views Thread by Edvard Majakari | last post: by
2 posts views Thread by Greg Lindstrom | last post: by
4 posts views Thread by Livin | last post: by
6 posts views Thread by MikeSwann | last post: by
reply views Thread by Greg Corradini | last post: by
3 posts views Thread by JamesB | last post: by
19 posts views Thread by Dr Mephesto | last post: by
reply views Thread by Solution2021 | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.