SQL 如何从 Oracle 中的表中获取列名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/452464/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I get column names from a table in Oracle?
提问by Paxenos
I need to query the database to get the column names, not to be confused with data in the table. For example, if I have a table named EVENT_LOGthat contains eventID, eventType, eventDesc, and eventTime, then I would want to retrieve those field names from the query and nothing else.
我需要查询数据库以获取列名，不要与表中的数据混淆。举例来说，如果我有一个名为表中EVENT_LOG包含eventID，eventType，eventDesc，和eventTime，然后我会想检索查询，并没有其他的字段名。
I found how to do this in:
我发现如何做到这一点：
But I need to know: how can this be done in Oracle?
但我需要知道：如何在Oracle 中做到这一点？
回答by baretta
You can query the USER_TAB_COLUMNS table for table column metadata.
您可以查询 USER_TAB_COLUMNS 表以获取表列元数据。
SELECT table_name, column_name, data_type, data_length
FROM USER_TAB_COLUMNS
WHERE table_name = 'MYTABLE'
回答by Eppz
In SQL Server...
在 SQL Server 中...
SELECT [name] AS [Column Name]
FROM syscolumns
WHERE id = (SELECT id FROM sysobjects WHERE type = 'V' AND [Name] = 'Your table name')
Type = 'V' for views Type = 'U' for tables
Type = 'V' 表示视图 Type = 'U' 表示表格
回答by Sergey Golovchenko
You can do this:
你可以这样做：
describe EVENT_LOG
or
或者
desc EVENT_LOG
Note: only applicable if you know the table name and specifically for Oracle.
注意：仅适用于您知道表名且专门针对 Oracle 的情况。
回答by Jom
For SQL Server 2008, we can use information_schema.columns for getting column information
对于 SQL Server 2008，我们可以使用 information_schema.columns 来获取列信息
SELECT *
FROM information_schema.columns
WHERE table_name = 'Table_Name'
ORDER BY ordinal_position
回答by shieldstroy
For SQLite I believe you can use something like the following:
对于 SQLite，我相信您可以使用以下内容：
PRAGMA table_info(table-name);
Explanation from sqlite.org:
来自 sqlite.org 的解释：
This pragma returns one row for each column in the named table. Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column. The "pk" column in the result set is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key.
此编译指示为命名表中的每一列返回一行。结果集中的列包括列名、数据类型、列是否可以为 NULL 以及列的默认值。结果集中的“pk”列对于不属于主键的列为零，并且对于属于主键的列是主键中的列的索引。
See also: Sqlite.org Pragma Table Info
回答by Jon Ericson
That information is stored in the ALL_TAB_COLUMNSsystem table:
该信息存储在ALL_TAB_COLUMNS系统表中：
SQL> select column_name from all_tab_columns where table_name = 'DUAL';
DUMMY
Or you could DESCRIBEthe table if you are using SQL*PLUS:
或者DESCRIBE，如果您使用 SQL*PLUS，则可以使用该表：
SQL> desc dual
Name Null? Type
----------------------------------------------------- -------- ---------------------- -------------
DUMMY VARCHAR2(1)
回答by Sasha
The other answers sufficiently answer the question, but I thought I would share some additional information. Others describe the "DESCRIBE table" syntax in order to get the table information. If you want to get the information in the same format, but without using DESCRIBE, you could do:
其他答案足以回答这个问题，但我想我会分享一些额外的信息。其他人描述了“DESCRIBE table”语法以获取表信息。如果您想以相同的格式获取信息，但不使用 DESCRIBE，您可以执行以下操作：
SELECT column_name as COLUMN_NAME, nullable || ' ' as BE_NULL,
SUBSTR(data_type || '(' || data_length || ')', 0, 10) as TYPE
FROM all_tab_columns WHERE table_name = 'TABLENAME';
Probably doesn't matter much, but I wrote it up earlier and it seems to fit.
可能没什么关系，但我之前写过，似乎很合适。
回答by jampez77
For Oracle
甲骨文
SELECT column_name FROM user_tab_cols WHERE table_name=UPPER('tableName');
回答by Rohit
describe YOUR_TABLE;
In your case :
在你的情况下：
describe EVENT_LOG;
回答by ssamuel68
For MySQL, use
对于 MySQL，请使用
SELECT column_name
FROM information_schema.columns
WHERE
table_schema = 'Schema' AND table_name = 'Table_Name'
