How can we get the table schema information in MYSQL 5.1.34?

Like we use in ORACLE to get it as:

OCIDescribeAny() // //get the describe handle for the table to be described.

OCIAttrGet() // to get parameter handle

OCIAttrGet() ////get the number of columns in the table

You can select that information from the INFORMATION_SCHEMA or simply run "SHOW CREATE TABLE"

from the INFORMATION_SCHEMA views

From INFORMATION_SCHEMA I will not get the below information.

I want all the information like colname, datatype, collength, precision, scale, IsNullable.

I think we can write the sub query as

select * from and we fetch it from output of "desc table" query.


How do I achieve this one?

Re: Change detection in MySQL.
How do I get the Parameter information of table?
I have table like

mysql> desc account;

+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| acct_num | int(11) | NO | PRI | NULL | |
| amount | decimal(2,0) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+

I want all the information like colname, datatype, collength, precision, scale, Nullable. I tried using the

MYSQL_FIELD *fields;
unsigned int num_fields;

pResult = mysql_store_result(MySQLDB);
num_fields= mysql_num_fields(pResult);
fields = mysql_fetch_fields(pResult);
for( i = 0; i < num_fields; i++)
{
printf("Field %u is %s\n", i, fields[i].name);
}

It is giving me output like :
Field
Type
NULL
KEY
Default

row = mysql_fetch_row(pResult);
it will give me output as :
acct_num

Which I wanted to get.

But other information not gettting like datatype, precision, scale, nullable.

If I fire a query select * from account;It will gives me What I want in fields.
But it is not good if the there are 100 table which has millions of records and I want information of each table.
Is ther any other way to get only the reuqired information?

You've already asked this question in this thread and you were given your answer - why waste peoples time (either mine for answering your original question or Rudy's for answering this one)?

Yes you will, but you will have to read the documentation.

Here is another hint: check out the COLUMNS

How do I fetch the output of this query?
mysql> select * from INFORMATION_SCHEMA.COLUMNS where table_name='account');

In coding I run the query using mysql_real_query() whose result is stored in

pResult = mysql_store_result(pMySQLDB);
fields = mysql_fetch_field(pResult);

But in field I get TABLE_SCHEMA, TABLE_NAME that I don't want.
I want the value of these field like "amit, "account".

Can you please tell me how do I fetch the data what I want from the query output?

stop using the dreaded, evil "select star"

What r937 means: by simply listing the columns you want

For details see the manual: http://dev.mysql.com/doc/refman/5.0/en/select.html

Using SELECT * for anything else than ad-hoc queries is a bad idea

Thanks you very much. I solved my problem.

How do I get the native data type like we get in ORACLE using

OCIAttrGet(colHandle, OCI_DTYPE_PARAM, (dvoid *) &dataType, 0,
OCI_ATTR_DATA_TYPE, m_errorHandle);

It will return &datatype value for:
VARCHAR2 - 1
NUMBER - 2
Date - 12

How do we achieve this in MYSQL 5.1?

I dunno
does the MySQL manual provide any assistance?

I saw in manual but not found any pointer. There is no any point described for native data types value.

what happens if you try to run the following query
OK so MySQL doesn't have an integer value describing the column type it returns a text/string/char.. but it does tell you what data type its using. there is no need to work through each data type to find out what internal value the SQL engine is using