SELECT   q_tab.Col_Name As "Name",   q_tab.Null_Yn As "Null?",   q_tab.Data_Type As "Type",   q_tab.comments
FROM    (SELECT
   ct.column_id,
   Ct.Column_Name As Col_Name,
   DECODE(ct.nullable,'N','NOT NULL','') as Null_yn,
   RTRIM(SUBSTR(DECODE(ct.data_type,
   'RAW',ct.data_type||'('||ct.data_length||')',
   'VARCHAR2',ct.data_type||'('||ct.data_length||')',
   'CHAR',ct.data_type||'('||ct.data_length||')',
   'NUMBER',ct.data_type|| DECODE(ct.data_precision,NULL,'','('||ct.data_precision||
     DECODE(Nvl(Ct.Data_Scale,0),0,'',','||Ct.Data_Scale)||')'),
   ct.Data_Type),1,20)) As data_Type,
   RTRIM(c.comments) comments
  FROM all_tab_columns ct, all_col_comments c
  WHERE ct.owner = c.owner (+)
  AND ct.table_name = c.table_name (+)
  AND Ct.Column_Name = C.Column_Name (+)
  AND ct.Owner = 'SYS'
  AND ct.Table_Name = Upper('&p_table')
  ORDER BY ct.Column_Id) q_tab;

Description of All_Tab_Columns


SELECT    q_tab.col_name as "Name",   q_tab.null_yn as "Null?",   q_tab.data_type as "Type",   q_tab.comments
FROM    (SELECT
   ct.column_id,
   Ct.Column_Name As Col_Name,
   DECODE(ct.nullable,'N','NOT NULL','') as Null_yn,
   RTRIM(SUBSTR(DECODE(ct.data_type,
   'RAW',ct.data_type||'('||ct.data_length||')',
   'VARCHAR2',ct.data_type||'('||ct.data_length||')',
   'CHAR',ct.data_type||'('||ct.data_length||')',
   'NUMBER',ct.data_type|| DECODE(ct.data_precision,NULL,'','('||ct.data_precision||
    DECODE(Nvl(Ct.Data_Scale,0),0,'',','||Ct.Data_Scale)||')'),
   ct.Data_Type),1,20)) As data_Type,
   RTRIM(c.comments) comments
  FROM all_tab_columns ct, all_col_comments c
  WHERE ct.owner = c.owner (+)
   AND ct.table_name = c.table_name (+)
   AND ct.column_name = c.column_name (+)
   AND ct.Owner = user
   And ct.Table_Name = Upper('&p_table')
   ORDER BY ct.Column_Id) q_tab;

In oracle database "DESC table_name" shows the column names and their types (number, Char(1), varchar2(10), date etc.). The same can done by the above scripts. These scripts also gives the column description if defined during table creation. This script is for table/view description only. The "DESC object_name" will show the object definition of any oracle database object.




  7603