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
What is the SQL command to return the field names of a table?
MySQL 3 and 4 (and 5):
desc tablename
which is an alias for
show fields from tablename
SQL Server (from 2000) and MySQL 5:
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'tablename'
Completing the answer: like people below have said, in SQL Server you can also use the stored procedure sp_help
exec sp_help 'tablename'
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 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'
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;
Oracle query to fetch column names
The Oracle equivalent for information_schema.COLUMNS
is USER_TAB_COLS
for tables owned by the current user, ALL_TAB_COLS
or DBA_TAB_COLS
for tables owned by all users.
Tablespace is not equivalent to a schema, neither do you have to provide the tablespace name.
Providing the schema/username would be of use if you want to query ALL_TAB_COLS
or DBA_TAB_COLS
for columns OF tables owned by a specific user. in your case, I'd imagine the query would look something like:
String sqlStr= "
SELECT column_name
FROM all_tab_cols
WHERE table_name = 'USERS'
AND owner = '" +_db+ "'
AND column_name NOT IN ( 'PASSWORD', 'VERSION', 'ID' )"
Note that with this approach, you risk SQL injection.
EDIT: Uppercased the table- and column names as these are typically uppercase in Oracle; they are only lower- or mixed case if created with double quotes around them.
Related Topics
Oracle SQL:Get All Integers Between Two Numbers
Select Random Row from a Postgresql Table with Weighted Row Probabilities
Running Total by Grouped Records in Table
SQL Server Row_Number() on SQL Server 2000
SQL Server Export to Excel with Openrowset
Pivot Without Aggregate Function in Mssql 2008 R2
Oracle - Update Join - Non Key-Preserved Table
How to Call Oracle Md5 Hash Function
How to Determine If a String Is Numeric in SQL
What Is the Internal Representation of Datetime in SQL Server
SQL Server 2008 Iif Statement Does Not Seem Enabled
Insert Multiple Rows into Db2 Database
Show Create Table' Equivalent in Oracle SQL
Django Prefetch_Related with Limit
How to Join Two Tables But Only Return Rows That Don't Match