MySQL Query to Get Column Names

MySQL query to get column names?

The best way is to use the INFORMATION_SCHEMA metadata virtual database. Specifically the INFORMATION_SCHEMA.COLUMNS table...

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_SCHEMA`='yourdatabasename' 
    AND `TABLE_NAME`='yourtablename';

It's VERY powerful, and can give you TONS of information without need to parse text (Such as column type, whether the column is nullable, max column size, character set, etc)...

Oh, and it's standard SQL (Whereas SHOW ... is a MySQL specific extension)...

For more information about the difference between SHOW... and using the INFORMATION_SCHEMA tables, check out the MySQL Documentation on INFORMATION_SCHEMA in general...

Get table column names in MySQL?

You can use DESCRIBE:

DESCRIBE my_table;

Or in newer versions you can use INFORMATION_SCHEMA:

SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

Or you can use SHOW COLUMNS:

SHOW COLUMNS FROM my_table;

Or to get column names with comma in a line:

SELECT group_concat(COLUMN_NAME)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

How can I find all the tables in MySQL with specific column names in them?

To get all tables with columns columnA or ColumnB in the database YourDatabase:

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('columnA','ColumnB')
        AND TABLE_SCHEMA='YourDatabase';

MySQL get column names from a query

Why do you need this?

If you want to use it in a procedure or similar, then you are already typed in the column names. If it is a dynamically generated query, use that method which generates the query to get the column names.

If you want to use them in your program, most languages provides functionality to get the resultsets column information.

PDOStatement::getColumnMeta ( int $column ) in PHP with PDO
OdbcDataReader.GetSchemaTable() in .NET

This solution is a hack! (and not recommended and also not tested, just a suggestion!)

Create a temporary table based on the query (SELECT .... INTO #temptable) (possibly with a where clause which never evaulates to true (WHERE 0=1), then query that temp tables metadata from INFORMATION_SCHEMA.COLUMNS

How to get column names of two tables in mysql?

SELECT COLUMN_NAME as column_name 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE (TABLE_NAME = 'product' AND COLUMN_NAME NOT IN ('ean','jan','isbn','mpn','upc'))
   OR (TABLE_NAME = 'other_table' AND COLUMN_NAME NOT IN ('something_else'))

MYSQL query table using column position, NOT column name

To answer your question, no, there is no syntax in SQL to reference the column by its position. This goes back to relational theory, in the sense that a table is a set of columns, and members of a set are unordered.

You will either have to know the column name, or else query it from the database:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='sale' 
  AND ORDINAL_POSITION=1;

It looks like you are trying to design a query that updates a row by primary key, by assuming the first column is the primary key. The primary key isn't necessarily the first column. It isn't necessarily an integer. It isn't necessarily a single column.

So you are already making assumptions about the table definition. You might as well assume the primary key column is named id or some other convention.

How to get column names with where condition included?

You can use information_schema.columns table instead of SHOW COLUMNS statement. The information_schema.columns table is the same as SHOW COLUMNS, but you can work with result-set as with ordinary table. For example -

SELECT * FROM information_schema.columns
WHERE table_schema = 'table name' AND table_name = 'table name';

Specify columns you need and WHERE filter.

Get Column Names with SQL Query

include 'connection.php';

$result = $connection->query("select * from users");
while ($row = mysqli_fetch_field($result)) {
    $array[] = $row;
}

In $array you will get all column name

How to get column name by index using MySQL?

use table information_schema.columns

select column_name 
from information_schema.columns 
where table_name = 'my_table_name' and ordinal_position = 2;

---

Related Topics