List:General Discussion« Previous MessageNext Message »
From:Gordon Date:March 17 2006 3:55pm
Subject:RE: getting table metadata
View as plain text  
What you are looking for is the INFORMATION_SCHEMA views, but they are not
available before 5.0. Until then you have to parse the "show create table"
or "DESCRIBE tablename" may be easier to parse 

mysql> describe organizations;
+-------------------+-------------------------------------------------------
----------------+------+-----+---------------------+-------+
| Field             | Type
| Null | Key | Default             | Extra |
+-------------------+-------------------------------------------------------
----------------+------+-----+---------------------+-------+
| orgn_ID           | char(4)
| NO   | PRI |                     |       |
| orgn_Name         | char(50)
| YES  |     | NULL                |       |
| orgn_Billing_Type | enum('Bank Transfer','Credit
Card','Invoice','none','Purchase Order') | YES  |     | NULL
|       |
| orgn_Internal_ID  | char(15)
| YES  |     | NULL                |       |
| orgn_Active       | enum('Yes','No')
| NO   |     | Yes                 |       |
| orgn_Who          | char(4)
| NO   |     |                     |       |
| orgn_Timestamp    | timestamp
| YES  |     | CURRENT_TIMESTAMP   |       |
| orgn_Create       | datetime
| NO   |     | 2000-01-01 00:00:00 |       |
+-------------------+-------------------------------------------------------
----------------+------+-----+---------------------+-------+
8 rows in set (0.27 sec) 

-----Original Message-----
From: Yves Glodt [mailto:yves@stripped] 
Sent: Friday, March 17, 2006 9:22 AM
To: mysql@stripped
Subject: Re: getting table metadata

On Friday 17 March 2006 15:52, Martijn Tonies wrote:
> Hello Yves,

Hello Martijn,

> > is it possible to get information about tables by doing queries on some
>
> system
>
> > tables? I am using mysql version 4.1.11 on debian sarge.
> >
> > In my case I need to know which columns (names and types) a table has,
> > and
>
> how
>
> > the primary key is defined.
> >
> > How can I get this information out of mysql by only using sql ?
>
> Have a look at the SHOW commands in the documentation.

I know about the "show create table ..." but it doesn't really satisfy my 
needs... Is there really no other way apart of parsing "create table" 
statements?

(I needed to this with firebird, and found all I could dream of in the RDB$ 
tables)

Best regards,
Yves

> As for system tables, MySQL 4.1 hardly has any.
>
> Martijn Tonies
> Database Workbench - development tool for MySQL, and more!
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
getting table metadataYves Glodt17 Mar
  • Re: getting table metadataMartijn Tonies17 Mar
    • Re: getting table metadataYves Glodt17 Mar
      • Re: getting table metadataКосов Евгений17 Mar
      • RE: getting table metadataGordon17 Mar
  • Re: getting table metadatamysql17 Mar
  • Re: getting table metadataMartijn Tonies17 Mar
    • Re: getting table metadataYves Glodt17 Mar