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 Oracle?
You can query the USER_TAB_COLUMNS table for table column metadata.
SELECT table_name, column_name, data_type, data_length
FROM USER_TAB_COLUMNS
WHERE table_name = 'MYTABLE'
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
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...
Just get column names from hive table
If you simply want to see the column names this one line should provide it without changing any settings:
describe database.tablename;
However, if that doesn't work for your version of hive this code will provide it, but your default database will now be the database you are using:
use database;
describe tablename;
How to dynamically get column names from TypeORM?
As proposed in the issue on repository of that library (link) can be solved by using
connection.getMetadata("User").columns
Retrieve name of column from its Index in Pandas
I think you need index columns names by position (python counts from 0
, so for fourth column need 3
):
colname = df.columns[pos]
Sample:
df = pd.DataFrame({'A':[1,2,3],
'B':[4,5,6],
'C':[7,8,9],
'D':[1,3,5],
'E':[5,3,6],
'F':[7,4,3]})
print (df)
A B C D E F
0 1 4 7 1 5 7
1 2 5 8 3 3 4
2 3 6 9 5 6 3
pos = 3
colname = df.columns[pos]
print (colname)
D
pos = [3,5]
colname = df.columns[pos]
print (colname)
Index(['D', 'F'], dtype='object')
Get column names AND types using star macro in dbt
You can view the source for dbt_utils.star
here
Under the hood, it uses dbt_utils.get_filtered_columns_in_relation
. That macro also just returns column names. However! that macro uses the built-in adapter.get_columns_in_relation
, which returns a list of Column objects, which have a data_type
property.
So your code becomes:
{% set all_columns = adapter.get_columns_in_relation(
ref("my_table")
) %}
{% set except_col_names=["a", "b", "c"] %}
select
date_trunc('week', day) as week,
name,
{%- for col in all_columns if col.name not in except_col_names %}
{% if col.data_type == 'BOOLEAN' %}
max({{ col.name|lower }}) as {{ col.name|lower }}{%- if not loop.last %},{{ '\n ' }}{% endif %}
{% endif %}
{%- endfor %}
from {{ ref('my_table_name') }}
group by 1, 2
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.
Related Topics
Select Random Row for Each Group
Dealing with System.Dbnull in Powershell
Getting All the Children of a Parent Using Mssql Query
MySQL Scoping Problem with Correlated Subqueries
How to Migrate an Existing Postgres Table to Partitioned Table as Transparently as Possible
Update Values in Identity Column
Sql: Combine Select Count(*) from Multiple Tables
How to Swap Column Values in SQL Server 2008
Postgres Recursive Query with Row_To_JSON
Date/Timestamp to Record When a Record Was Added to the Table
Oracle SQL: How to Use More Than 1000 Items Inside an in Clause
Using Hibernate's Criteria and Projections to Select Multiple Distinct Columns
Performance of Count SQL Function
Way to Insert Text Having ' (Apostrophe) into a SQL Table