How to View All the Metadata of Columns of a Table in Oracle Database

How to view all the Metadata of columns of a table in oracle database?

I want to know how I can retrieve the all column names, their data type, and any constraints that are defined for any column.

To do that you can query(depending on privileges granted to you) [user|all|dba]_tab_columns, [user|all|dba]_cons_columns, [user|all|dba]_constraints views.

Here is a quick example:

 select decode( t.table_name
              , lag(t.table_name, 1) over(order by t.table_name)
              , null
             , t.table_name ) as table_name -- <- just to eliminate 
      , t.column_name                       -- repeated tab_name    
      , t.data_type
      , cc.constraint_name
      , uc.constraint_type
   from user_tab_columns t
        left join user_cons_columns cc
          on (cc.table_name = t.table_name and
              cc.column_name = t.column_name)
        left join user_constraints uc
          on (t.table_name = uc.table_name and
              uc.constraint_name = cc.constraint_name )
 where t.table_name in ('EMPLOYEES', 'DEPARTMENTS');

Result:

TABLE_NAME    COLUMN_NAME       DATA_TYPE      CONSTRAINT_NAME   CONSTRAINT_TYPE
------------- ----------------- -------------- -------------------------------
DEPARTMENTS   LOCATION_ID       NUMBER         DEPT_LOC_FK       R
              DEPARTMENT_ID     NUMBER         DEPT_ID_PK        P
              DEPARTMENT_NAME   VARCHAR2       DEPT_NAME_NN      C
              MANAGER_ID        NUMBER         DEPT_MGR_FK       R
EMPLOYEES     SALARY            NUMBER         EMP_SALARY_MIN    C
              PHONE_NUMBER      VARCHAR2                            
              EMPLOYEE_ID       NUMBER         EMP_EMP_ID_PK     P
              DEPARTMENT_ID     NUMBER         EMP_DEPT_FK       R
              JOB_ID            VARCHAR2       EMP_JOB_FK        R
              MANAGER_ID        NUMBER         EMP_MANAGER_FK    R
              COMMISSION_PCT    NUMBER                              
              FIRST_NAME        VARCHAR2                            
              JOB_ID            VARCHAR2       EMP_JOB_NN        C
              HIRE_DATE         DATE           EMP_HIRE_DATE_NN  C
              EMAIL             VARCHAR2       EMP_EMAIL_NN      C
              LAST_NAME         VARCHAR2       EMP_LAST_NAME_NN  C
              EMAIL             VARCHAR2       EMP_EMAIL_UK      U

17 rows selected

Also to retrieve a complete specification(if needed) of a table, you can use dbms_metadata package and get_ddl function of that package:

select dbms_metadata.get_ddl('TABLE', 'EMPLOYEES') as table_ddl
  from dual;

 table_ddl
 --------------------------------------------------------------------------------

  CREATE TABLE "HR"."EMPLOYEES"
   ("EMPLOYEE_ID" NUMBER(6,0),
    "FIRST_NAME" VARCHAR2(20),
    "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
    "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
    "PHONE_NUMBER" VARCHAR2(20),
    "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
    "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
    "SALARY" NUMBER(8,2),
    "COMMISSION_PCT" NUMBER(2,2),
    "MANAGER_ID" NUMBER(6,0),
    "DEPARTMENT_ID" NUMBER(4,0),
     CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
     CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")

   -- ... other attributes

   )

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 do I get all the column data in a table

In Oracle you'd query the system views user_tab_cols, all_tab_cols or dba_tab_cols:

select * from user_tab_cols where table_name = :table_name;

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.

Oracle : which SQL command to get all details about a table?

I do something similar. I read those things from a SQL Server over OPENQUERY statements directly from Oracle DBs and save results into SQL Server tables to allow analysis of historic comparison schema information and changes.

So what you have to do with the resultsets of the following queries is to store them (regulary) somehow and add some kind of unique / primary key or timestamp to it, in order to distinguish between your different scans.

Leaving away the SQL Server specific code stuff, those are the basic oracle sql queries I use so far:

--Tables
SELECT table_name, owner, Tablespace_name, Num_Rows 
FROM all_tables WHERE tablespace_name is not NULL 
AND owner not in ('SYS', 'SYSTEM') 
ORDER BY  owner, table_name;

--Columns
SLECT OWNER, TABLE_NAME, Column_name, Data_type, data_length, data_precision, NULLABLE, character_Set_Name  
From all_tab_cols 
where USER_GENERATED = 'YES' 
AND owner not in ('SYS', 'SYSTEM');

--Indexes
select Owner, index_name, table_name, uniqueness,BLEVEL,STATUS from ALL_INDEXES
WHERE owner not in ('SYS', 'SYSTEM') 

--Constraints
select owner, constraint_name, constraint_type, table_name, search_condition, status, index_name, index_owner 
From all_constraints 
WHERE generated = 'USER NAME'
AND owner not in ('SYS', 'SYSTEM') 

--Role Previleges
select grantee, granted_role, admin_option, delegate_option, default_role, common
From DBA_ROLE_PRIVS

--Sys Privileges
select grantee, privilege, admin_option, common
From DBA_SYS_PRIVS 

Get column name, data type, size and comments of Oracle table

You need to add column name connection:

SELECT all_tab.column_name,
       all_tab.data_type,
       all_tab.data_length,
       (SELECT COMMENTS
          FROM user_col_comments t
         where t.TABLE_NAME = all_tab.TABLE_NAME
           and t.COLUMN_NAME = all_tab.column_name)
  FROM all_tab_columns all_tab
 WHERE all_tab.TABLE_NAME = 'MY_TABLE'

Retrieve Oracle Metadata with Internal Data

You cannot achieve this directly with SQL. You need PL/SQL + Execute Immediate to do the job.

• create a table with your expected columns
• then use for each loop to count the max length on each column.

Which metadata table in Oracle has detailed information about IDENTITY columns?

The catalog view USER_TAB_IDENTITY_COLS (or ALL_ or DBA_ depending on what you need and what privileges you have) will give you the information you need.

However, if you want to distinguish between BY DEFAULT and BY DEFAULT ON NULL you will need to query the *_TAB_COLUMNS views.

Neither view has ALL the possible information one might want - you will need to query both views to get all the details.

create table s ( x number generated always             as identity);
create table t ( y number generated by default         as identity);
create table u ( z number generated by default on null as identity);

select table_name, column_name, generation_type
from   user_tab_identity_cols
where  table_name in ('S', 'T', 'U')
;

TABLE_NAME   COLUMN_NAME  GENERATION_TYPE     
------------ ------------ ---------------  
S            X            ALWAYS 
T            Y            BY DEFAULT  
U            Z            BY DEFAULT 

select table_name, column_name, identity_column, default_on_null
from   user_tab_columns
where  table_name in ('S', 'T', 'U')
;

TABLE_NAME   COLUMN_NAME  IDENTITY_COLUMN DEFAULT_ON_NULL
------------ ------------ --------------- ---------------
S            X            YES             NO 
T            Y            YES             NO 
U            Z            YES             YES

---

Related Topics