SQL Oracle 查询以获取列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8739203/
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
Oracle query to fetch column names
提问by pri_dev
I have a mySQL query to get columns from a table like this:
我有一个 mySQL 查询来从这样的表中获取列：
String sqlStr="select column_name
from information_schema.COLUMNS
where table_name='users'
and table_schema='"+_db+"'
and column_name not in ('password','version','id')"
How do I change the above query in Oracle 11g database? I need to get columns names as a resultset for table 'users' excluding certain columns, specifying a schema. Right now I have all tables in my new tablespace, so do I specify tablespace name in place of schema name?
如何在 Oracle 11g 数据库中更改上述查询？我需要获取列名称作为表 'users' 的结果集，不包括某些列，指定模式。现在我的新表空间中有所有表，那么我是否指定表空间名称代替模式名称？
Also is there a generic HQL for this? In my new Oracle database (I am new to Oracle), I only have tablespace name, so is that equivalent to schema name (logically?)
还有一个通用的 HQL 吗？在我的新 Oracle 数据库中（我是 Oracle 的新手），我只有表空间名称，所以这相当于模式名称（逻辑上？）
回答by Sathyajith Bhat
The Oracle equivalent for information_schema.COLUMNSis USER_TAB_COLSfor tables owned by the current user, ALL_TAB_COLSor DBA_TAB_COLSfor tables owned by all users.
Oracle的等价物information_schema.COLUMNS是USER_TAB_COLS由当前用户拥有的表，ALL_TAB_COLS或者DBA_TAB_COLS为所有用户所拥有的表。
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_COLSor DBA_TAB_COLSfor columns OF tables owned by a specific user. in your case, I'd imagine the query would look something like:
如果您想查询ALL_TAB_COLS或查询DBA_TAB_COLS特定用户拥有的 OF 表列，则提供架构/用户名将很有用。在你的情况下，我想查询看起来像：
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.
请注意，使用这种方法，您可能会面临 SQL 注入的风险。
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.
编辑：大写表名和列名，因为它们在 Oracle 中通常是大写的；如果在它们周围使用双引号创建，它们只是小写或混合大小写。
回答by Karthik N G
The below query worked for me in Oracle database.
以下查询在 Oracle 数据库中对我有用。
select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME='MyTableName';
回答by Arsalan Sheikh
in oracle you can use
在oracle中你可以使用
desc users
to display all columns containing in users table
显示用户表中包含的所有列
回答by Md. Salman Fahad Famous
You may try this : ( It works on 11g and it returns all column name from a table , here test_tbl is the table name and user_tab_columns are user permitted table's columns )
你可以试试这个：（它适用于 11g 并返回表中的所有列名，这里 test_tbl 是表名，user_tab_columns 是用户允许的表的列）
select COLUMN_NAME from user_tab_columns
where table_name='test_tbl';
select COLUMN_NAME from user_tab_columns
where table_name='test_tbl';
回答by Altán
The only way that I was able to get the column names was using the following query:
我能够获取列名的唯一方法是使用以下查询：
select COLUMN_NAME
FROM all_tab_columns atc
WHERE table_name like 'USERS'
回答by yair
The query to use with Oracle is:
与 Oracle 一起使用的查询是：
String sqlStr="select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME='"+_db+".users' and COLUMN_NAME not in ('password','version','id')"
String sqlStr="select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME='"+_db+".users' and COLUMN_NAME not in ('password','version','id')"
Never heard of HQL for such queries. I assume it doesn't make sense for ORM implementations to deal with it. ORM is an Object Relational Mapping, and what you're looking for is metadata mapping... You wouldn't use HQL, rather use API methods for this purpose, or direct SQL. For instance, you can use JDBC DatabaseMetaData.
从未听说过用于此类查询的 HQL。我认为 ORM 实现处理它没有意义。ORM 是一种对象关系映射，而您正在寻找的是元数据映射……您不会使用 HQL，而是为此使用 API 方法或直接 SQL。例如，您可以使用 JDBC DatabaseMetaData。
I think tablespace has nothing to do with schema. AFAIK tablespaces are mainly used for logical internal technical purposes which should bother DBAs. For more information regarding tablespaces, see Oracle doc.
我认为表空间与模式无关。AFAIK 表空间主要用于应该困扰 DBA 的逻辑内部技术目的。有关表空间的更多信息，请参阅Oracle 文档。
回答by Ghadir Farzaneh
the point is that in toad u have to write table name capital, like this:
关键是在蟾蜍中你必须写表名大写，像这样：
select *
FROM all_tab_columns
where table_name like 'IDECLARATION';
回答by Dave C.
I find this one useful in Oracle:
我发现这在 Oracle 中很有用：
SELECT
obj.object_name,
atc.column_name,
atc.data_type,
atc.data_length
FROM
all_tab_columns atc,
(SELECT
*
FROM
all_objects
WHERE
object_name like 'GL_JE%'
AND owner = 'GL'
AND object_type in ('TABLE','VIEW')
) obj
WHERE
atc.table_name = obj.object_name
ORDER BY
obj.object_name,
atc.column_name;
回答by Kaushik Nayak
On Several occasions, we would need comma separated list of all the columns from a table in a schema. In such cases we can use this generic function which fetches the comma separated list as a string.
在某些情况下，我们需要以逗号分隔的模式中表中所有列的列表。在这种情况下，我们可以使用这个通用函数来获取逗号分隔的列表作为字符串。
CREATE OR REPLACE FUNCTION cols(
p_schema_name IN VARCHAR2,
p_table_name IN VARCHAR2)
RETURN VARCHAR2
IS
v_string VARCHAR2(4000);
BEGIN
SELECT LISTAGG(COLUMN_NAME , ',' ) WITHIN GROUP (
ORDER BY ROWNUM )
INTO v_string
FROM ALL_TAB_COLUMNS
WHERE OWNER = p_schema_name
AND table_name = p_table_name;
RETURN v_string;
END;
/
So, simply calling the function from the query yields a row with all the columns.
因此，只需从查询中调用该函数即可生成包含所有列的行。
select cols('HR','EMPLOYEES') FROM DUAL;
EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
Note:LISTAGGwill fail if the combined length of all columns exceed 4000characters which is rare. For most cases , this will work.
注意：LISTAGG如果所有列的组合长度超过4000罕见的字符，则会失败。对于大多数情况，这将起作用。
回答by Reza Rahimi
You can try this:
你可以试试这个：
describe 'Table Name'
描述“表名”
It will return all column names and data types
它将返回所有列名和数据类型
