How to Get Column Names from a Query in SQL Server

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

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

MYSQL, MS SQL and Postgresql (thanks @christophe)

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

PIVOT the results if you need column names in one line

How do you return the column names of a table?

Not sure if there is an easier way in 2008 version.

USE [Database Name]
SELECT COLUMN_NAME,* 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName' AND TABLE_SCHEMA='YourSchemaName'

How to get column names from a query?

You can try this on a tJavaRow, following your DBInput component :

for (java.lang.reflect.Field field: row1.getClass().getDeclaredFields()) {
context.columnName = field.getName();
      System.out.println("Field name is " + context.columnName );      
           }

Spotted on talend help center here : https://community.talend.com/t5/Design-and-Development/resolved-how-to-get-the-column-names-in-a-data-flow/td-p/99172

You can extend this, and put the column list on your outputflow :

//add this inside the loop, and 'columnNames' as an output row in tJavaRow schema

             output_row.columnNames+=context.columnName+";";

With a tNormalize after tJavaRow, you shoud get the expected result.

Get column names of all tables in SQL

You can use INFORMATION_SCHEMA.COLUMNS:

select c.*
from INFORMATION_SCHEMA.COLUMNS c;

This has name, type, and a lot of other information for all tables in a database -- note, not on a server but in a database.

How to get few column names among all columns in the one table with query in sql server 2008

I assumed this is SQL Server, so the below queries might not work on other RDBMS's).

If your question is how to find only the column names you need (presuming you know in advance which ones those are), you would have to do something like this:

SELECT 
    COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS 
WHERE 
    TABLE_NAME='Submenu'
    AND COLUMN_NAME IN ('Column1', 'Column2', 'Column3')

At this moment, you basically are requesting a list of any column within your table, without any restrictions whatsoever.

Alternatively, if you're looking only for the first three column names, this would work:

SELECT TOP 3
    COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS 
WHERE 
    TABLE_NAME='Submenu'
ORDER BY
    ORDERINAL_POSITION

In the latter case, you will have to determine how you want to sort the column names though (either by using something like ORDER BY COLUMN_NAME, in case you want them listed alphabetically, or ORDER BY ORDERINAL_POSITION in case you're trying to get them in the order they appear in the table).

If this is not what you meant, please elaborate on what you are trying to achieve.

---

Related Topics