Get Table Column Names in MySQL

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

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...

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

Get Table Name, Column Name, Data Type & Character Maximum Lenght mySQL

You can put more than one column in a SELECT statement..

Select Table_Name, Column_Name, Data_Type, Character_Maximum_Length 
from INFORMATION_SCHEMA.Columns

Use AS if you want to rename the columns you see (SELECT table_name AS "Table Name" .... I'd recommend not putting spaces in them though

MySQL - Get column names from table and display them (using PHP)

Your query is already correct. But you lack something in the fetching:

while($row = mysqli_fetch_object($entry))
{
    echo $row->Field . '<br/>';
              // ^ access the objects properties
}

Column names from MySQL table in the right order

The column ordinal_position has the original column ordering information:

SELECT column_name
FROM information_schema.columns 
WHERE table_name = 'сведения о фильме' AND table_schema = 'videokassety2'
ORDER BY ordinal_position;

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

How can I get column names from a table in SQL Server?

You can obtain this information and much, much more by querying the Information Schema views.

This sample query:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

Can be made over all these DB objects:

• CHECK_CONSTRAINTS
• COLUMN_DOMAIN_USAGE
• COLUMN_PRIVILEGES
• COLUMNS
• CONSTRAINT_COLUMN_USAGE
• CONSTRAINT_TABLE_USAGE
• DOMAIN_CONSTRAINTS
• DOMAINS
• KEY_COLUMN_USAGE
• PARAMETERS
• REFERENTIAL_CONSTRAINTS
• ROUTINES
• ROUTINE_COLUMNS
• SCHEMATA
• TABLE_CONSTRAINTS
• TABLE_PRIVILEGES
• TABLES
• VIEW_COLUMN_USAGE
• VIEW_TABLE_USAGE
• VIEWS

---

Related Topics