CODEWITHSUNDEEP
mysql
Wern Ancheta
Wern Ancheta

Reputation: 23297

How to select only fieldname when using show columns query in mysql

I use this query to select fields in a given table. Is it possible to select only the fieldname and not the whole structure of the table?

SHOW COLUMNS FROM student

Upvotes: 9

Views: 15869

Answers (3)

Csongor Halmai
Csongor Halmai

Reputation: 3885

The solution mentioned here earlier is not the correct one. Example:

CREATE DATABASE db1;
CREATE DATABASE db2;
CREATE TABLE db1.t ( id_1 INT);
CREATE TABLE db2.t ( id_2 INT);
SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME  ='t';

This will display: 

+-------------+
| COLUMN_NAME |
+-------------+
| id_1        |
| id_2        |
+-------------+

suggesting that the table t has two column which is obviously not true. This query lists all the columns of the tables called t in all of your databases.

Instead, you should specify which database contains the table t you want to select the column names from:

SELECT COLUMN_NAME 
    FROM information_schema.COLUMNS 
    WHERE 
        TABLE_NAME = 't' AND 
        TABLE_SCHEMA = 'db1';

Upvotes: 1

Prakash Kunwar
Prakash Kunwar

Reputation: 797

select COLUMN_NAME FROM TABLE_NAME

FOR EXAMPLE: ROLLNO is a column_Name of table Student....

select ROLLNO from Student

Upvotes: -3

Marc B
Marc B

Reputation: 360592

You're trying to determine the table structure? You can query MySQL's information_schema database directly for the fieldnames:

select COLUMN_NAME from information_schema.COLUMNS where TABLE_NAME='student';

Upvotes: 15

Related Questions