Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2009
    Posts
    41

    Unanswered: Default How to get Table schema information ?

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You can select that information from the INFORMATION_SCHEMA or simply run "SHOW CREATE TABLE"

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by amitbora27
    How can we get the table schema information in MYSQL 5.1.34?
    from the INFORMATION_SCHEMA views

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2009
    Posts
    41

    Table schema information.

    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?

  5. #5
    Join Date
    May 2009
    Posts
    41
    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?

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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)?

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by amitbora27
    From INFORMATION_SCHEMA I will not get the below information.

    I want all the information like colname, datatype, collength, precision, scale, IsNullable.
    Yes you will, but you will have to read the documentation.

    Here is another hint: check out the COLUMNS

  8. #8
    Join Date
    May 2009
    Posts
    41
    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?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by amitbora27
    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"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by amitbora27
    Can you please tell me how do I fetch the data what I want from the query output?
    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

  11. #11
    Join Date
    May 2009
    Posts
    41
    Thanks you very much. I solved my problem.

  12. #12
    Join Date
    May 2009
    Posts
    41
    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?

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I dunno
    does the MySQL manual provide any assistance?
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    May 2009
    Posts
    41
    I saw in manual but not found any pointer. There is no any point described for native data types value.

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what happens if you try to run the following query
    Code:
    select TABLE_NAME, COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE from information_schema.columns;
    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
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •