Oracle query to fetch column names

Question

I have a mySQL query to get columns from a table like this   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   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

User · Answer

SELECT   FROM  lt SCHEMA NAME TABLE NAME gt  WHERE ROWNUM   0  --  Note that  this is Query Result  a ResultSet  This is exportable to other formats  And  you can export the Query Result to Text format  Export looks like below when I did SELECT   FROM SATURN SPRIDEN WHERE ROWNUM   0          SPRTELE PIDM    SPRTELE SEQNO   SPRTELE TELE CODE   SPRTELE ACTIVITY DATE   SPRTELE PHONE AREA      SPRTELE PHONE NUMBER    SPRTELE PHONE EXT   SPRTELE STATUS IND      SPRTELE ATYP CODE   SPRTELE ADDR SEQNO      SPRTELE PRIMARY IND     SPRTELE UNLIST IND      SPRTELE COMMENT     SPRTELE INTL ACCESS     SPRTELE DATA ORIGIN     SPRTELE USER ID     SPRTELE CTRY CODE PHONE     SPRTELE SURROGATE ID    SPRTELE VERSION     SPRTELE VPDI CODE  DESCRIBE  lt TABLE NAME gt   --  Note  This is script output

User · Answer

in oracle you can use   desc users   to display all columns containing in users table

User · Answer

You can try this   describe  Table Name   It will return all column names and data types

User · Answer

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

User · Answer

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       select  COLUMN NAME  from user tab columns where table name  test tbl

User · Answer

The below query worked for me in Oracle database   select COLUMN NAME from ALL TAB COLUMNS where TABLE NAME  MyTableName

User · Answer

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

User · Answer

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

User · Answer

I find this one useful in 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

User · Answer

You can use the below query to get a list of table names which uses the specific column in DB2   SELECT TBNAME                 FROM SYSIBM SYSCOLUMNS        WHERE NAME LIKE   COLUMN NAME      Note   Here replace the COLUMN NAME with the column name that you are searching for

User · Answer

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  LISTAGG will fail if the combined length of all columns exceed 4000 characters which is rare  For most cases   this will work

User · Answer

The query to use with Oracle is   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   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

[sql] Oracle query to fetch column names

Examples related to sql

Examples related to oracle