Question 63962 in PhpMyAdmin Question posted in
The official documentation can be found here.

How can I get all column names from a table from a specific database in SQL Server? – Phpmyadmin

This query gives all the column names from all the databases.

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

But I need to get all the column names from a table from a specific database. Because I have multiple tables with the same name. How to do that? Is there anything like

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATABASE_NAME = 'database_name' TABLE_NAME = 'table_name'

I am using xampp server and phpMyAdmin.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    This is the SQL for my expected result

    SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='database_name' AND TABLE_NAME = 'table_name';

  2. 0

    Quote the fine manual:

    26.3.8 The INFORMATION_SCHEMA COLUMNS Table

    …

    The COLUMNS table has these columns:

    TABLE_CATALOG

The name of the catalog to which the table containing the column belongs. This value is always def.

TABLE_SCHEMA

The name of the schema (database) to which the table containing the column belongs.

    …

    ..you should see it when you do SELECT * ?

    Login or Signup to reply.
  3. 0

    You want something like this.

    SELECT TABLE_NAME, COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'whatever'
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

    The MySQL INFORMATION_SCHEMA uses columns named TABLE_SCHEMA to hold the database for each table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search