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

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.

CodePudding user response：

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 * ?

CodePudding user response：

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.

CodePudding user response：

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