How to get the mysql table columns data type?

I want to get the column data type of a mysql table.

Thought I could use MYSQLFIELD structure but it was enumerated field types.

Then I tried with mysql_real_query()

The error which i am getting is query was empty

How do I get the column data type?

dynamic columns and data type in mysql

I am a mysql novice and have been handed task of creating a table in mysql – we don’t know the column name or the data type or the length of the data type. We get data, xml, files from various reporti

MySql TABLE data type

MS SQL Server has a TABLE data type which can be used in stored procedures, Does anybody know if MySQL has an equivalent data type? I’ve had a look over the docs but can’t seem to find anything, so I

get mysql table columns description

I am trying to get my table’s columns description I tried fetch_fields() but it doesn’t fetch the description (just name, type, length …) $finfo = $result->fetch_fields(); foreach ($finfo as $va

Mysql – how to get data for two foreign key columns of same table

I have two tables in my database Table: users with columns: id, username, email etcs Table: users_messages with columns: id, to, from, messages, sent_time Columns to and from are keys users from us

How to get total number of columns, column name, data type of a SQL in oracle

I want to know how to get total number of columns, individual column name and its corresponding data type in oracle. For example SQL := ‘SELECT UT.TABLESPACE_NAME, UT.TABLE_NAME, UT.STATUS, UT.NUM_RO

How to post data from last x rows with z columns from a table? (MYSQL/PHP)

In a database, is a table, atable, with 3 columns, id (autoincremented), title, and description. How can you get the last 6 rows in the table and post the data as below: <div class=myclass> &

How can I get the type, length and comment about columns in MySQL database using PDO?

I have created this function to get information about the tables, and their corresponding columns, in my MySQL database: class SQL extends DB { public static function showTables($alias=”, $table_name

Inserting data into multiple columns in MySQL table from python

For some reason, I’m unable to insert data into multiple columns in mysql table. As soon as I insert into the first column (anyone column), I find it difficult to insert into other columns. I noticed

How to insert data into swing table whose columns are dynamically defined (data from a mysql database)?

I have a table and on a opening of a frame the table receives its columns and their respective headers from a table in mysql database. How can i insert all the values from the sql database table into

how to get data collectively from table columns

i have such a table: r_id date recipe_name 1 2012-05-20 Cheese Bread 1 2012-05-21 Cheese pie 2 2012-05-20 Spanish Bread I would like to get all the data under r_id 1 to be in a single row how can i d

Answers

The query below returns a list of information about each field, including the MySQL field type. Here is an example:

SHOW FIELDS FROM tablename
/* returns "Field", "Type", "Null", "Key", "Default", "Extras" */

See this manual page.

You can use the information_schema columns table:

SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE table_name = 'tbl_name' AND COLUMN_NAME = 'col_name';

To get data types of all columns:

describe table_name

or just a single column:

describe table_name column_name

Refer this link

mysql> SHOW COLUMNS FROM mytable FROM mydb;
mysql> SHOW COLUMNS FROM mydb.mytable;

Hope this may help you

To view the data types in a column:

  1. Select the table you want to view the data types and right click; then

  2. Select Alter Table

First select the Database using use testDB; then execute

desc `testDB`.`images`;
-- or
SHOW FIELDS FROM images;

Output:

How to get the mysql table columns data type?

Query to find out all the datatype of columns being used in any database

SELECT distinct DATA_TYPE FROM INFORMATION_SCHEMA.columns 
WHERE table_schema = '<db_name>' AND column_name like '%';

SHOW COLUMNS FROM mytable

Self contained complete examples are often useful.

<?php
  // The server where your database is hosted                 localhost
  // The name of your database                                mydatabase
  // The user name of the database user                       databaseuser
  // The password of the database user                        thesecretpassword
  // Most web pages are in utf-8 so should be the database    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
  try
  {
    $pdo = new PDO("mysql:host=localhost;dbname=mydatabase", "databaseuser", "thesecretpassword", array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));  
  }
    catch(PDOException $e)
  {
    die('Could not connect: ' . $e->getMessage());
  }

  $sql   = "SHOW COLUMNS FROM mytable";
  $query = $pdo->prepare($sql);
  $query->execute();

  $err = $query->errorInfo();
  $bug = $err[2];

  if ($bug != "") { echo "<p>$bug</p>"; }

  while ($row = $query->fetch(PDO::FETCH_ASSOC))
  {
    echo "<pre>" . print_r($row, true) . "</pre>";
  }

  /* OUTPUT SAMPLE  
  Array
  (
      [Field] => page_id
      [Type] => char(40)
      [Null] => NO
      [Key] => 
      [Default] => 
      [Extra] => 
  )

  Array
  (  
      [Field] => last_name
      [Type] => char(50)
      More ...
  */
?>

ResultSet rs = Sstatement.executeQuery(“SELECT * FROM Table Name”);

    ResultSetMetaData rsMetaData = rs.getMetaData();

    int numberOfColumns = rsMetaData.getColumnCount();
    System.out.println("resultSet MetaData column Count=" + numberOfColumns);

    for (int i = 1; i <= numberOfColumns; i++) {
     System.out.println("column number " + i);

      System.out.println(rsMetaData.getColumnTypeName(i));
    }

Most answers are duplicates, it might be useful to group them. Basically two simple options have been proposed.

First option

The first option has 4 different aliases, some of which are quite short :

EXPLAIN db_name.table_name;
DESCRIBE db_name.table_name;
SHOW FIELDS FROM db_name.table_name;
SHOW COLUMNS FROM db_name.table_name;

(NB : as an alternative to db_name.table_name, one can use a second FROM : db_name FROM table_name).

This gives something like :

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| product_id       | int(11)      | NO   | PRI | NULL    |       |
| name             | varchar(255) | NO   | MUL | NULL    |       |
| description      | text         | NO   |     | NULL    |       |
| meta_title       | varchar(255) | NO   |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+

Second option

The second option is a bit longer :

SELECT
  COLUMN_NAME, DATA_TYPE 
FROM
  INFORMATION_SCHEMA.COLUMNS 
WHERE
  TABLE_SCHEMA = 'db_name'
AND
  TABLE_NAME = 'table_name';

It is also less talkative :

+------------------+-----------+
| column_name      | DATA_TYPE |
+------------------+-----------+
| product_id       | int       |
| name             | varchar   |
| description      | text      |
| meta_title       | varchar   |
+------------------+-----------+

It has the advantage of allowing selection per column, though, using AND COLUMN_NAME = ‘column_name’ (or like).