CODEWITHSUNDEEP
mysql
krishnakumar
krishnakumar

Reputation: 2277

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?

Upvotes: 146

Views: 280158

Answers (12)

Skippy le Grand Gourou
Skippy le Grand Gourou

Reputation: 7694

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: In each case, you can also write FROM two times instead of db_name.table_name, example:

SHOW FIELDS FROM table_name FROM db_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).

Upvotes: 66

palakdeora
palakdeora

Reputation: 11

SELECT 
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME, 
COLUMN_NAME, 
DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS

Upvotes: 0

Tarkeshwar Prasad
Tarkeshwar Prasad

Reputation: 29

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='SCHEMA_NAME' AND COLUMN_KEY='PRI'; WHERE COLUMN_KEY='PRI';

Upvotes: 1

Prasant Kumar
Prasant Kumar

Reputation: 1058

Please use the below mysql query.

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
FROM information_schema.columns 
WHERE table_schema = '<DATABASE NAME>' 
AND table_name = '<TABLE NAME>' 
AND COLUMN_NAME = '<COLOMN NAME>'

MySql Query Result

Upvotes: 20

SRK
SRK

Reputation: 128

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));
}

Upvotes: 1

nbauers
nbauers

Reputation: 199

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 ...
  */
?>

Upvotes: 1

user3419013
user3419013

Reputation: 21

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 '%';

Upvotes: 2

UdayKiran Pulipati
UdayKiran Pulipati

Reputation: 6667

First select the Database using use testDB; then execute

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

Output:

Get Table Columns with DataTypes

Upvotes: 8

sonu thomas
sonu thomas

Reputation: 2161

Refer this link

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

Hope this may help you

Upvotes: 5

jondinham
jondinham

Reputation: 8511

To get data types of all columns:

describe table_name

or just a single column:

describe table_name column_name

Upvotes: 29

brian-brazil
brian-brazil

Reputation: 34122

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';

Upvotes: 158

James Skidmore
James Skidmore

Reputation: 50298

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.

Upvotes: 114

Related Questions