How to get MySQL table Column Names without MetaData

Often we need to list just the column name of the table without displaying the metadata. However, Until before MySQL 5.0 there was no way to get just the column names for any given table. Since MySQL 5.0 new table has been added information_schema. INFORMATION_SCHEMA provides access to database metadata.

Metadata is data about the data, such as the name of a database or table, the data type of a column, or access privileges. Other terms that sometimes are used for this information are data dictionary and system catalog.

INFORMATION_SCHEMA is the information database, the place that stores information about all the databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them.

In effect, MySQL has a database named INFORMATION_SCHEMA, although the server does not create a database directory with that name. It is possible to select INFORMATION_SCHEMA as the default database with a USE statement, but you can only read the contents of tables. You cannot insert, update, or delete from them. For more information you can view the MySQL reference here.

select column_name from information_schema.columns where table_name='emp' and table_schema='test';

This will give following output.

+-------------+
| column_name |
+-------------+
| id          |
| name        |
| salary      |
+-------------+

These are just the column names not metadata which would appear with.

desc emp;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | bigint(10)    | NO   | PRI | NULL    |       |
| name   | varchar(20)   | YES  |     | NULL    |       |
| salary | decimal(10,0) | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

Or with.

show columns from emp;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | bigint(10)    | NO   | PRI | NULL    |       |
| name   | varchar(20)   | YES  |     | NULL    |       |
| salary | decimal(10,0) | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

Be the first to comment

Leave a Reply

Your email address will not be published.


*


CommentLuv badge