Column Descriptions

[TopJack]

Is it possible to write a SQL SELECT statement that can retrieve in-built column descriptions from an Oracle(10) database?

Example:
SELECT * with column_headings
FROM "TABLE_NAME"

Any ideas ?

 

[r937]

you'll probably get better answers sooner in the oracle forum



r937.com | rudy.ca

 

[TopJack]

Ok, will do. Thanks.

 

[BJCooperIT]

Here is a script I use:

DROP TABLE test_it;
CREATE TABLE test_it (fld1 DATE NOT NULL, fld2 VARCHAR2(5), fld3 NUMBER(10), fld4 number(8,2));
COMMENT ON COLUMN test_it.fld1 is 'Date Added';
COMMENT ON COLUMN test_it.fld2 is 'Description';

SELECT --tab.owner,
       --tab.table_name,
       col.column_name "COLUMN",
       col.data_type   "TYPE",
       DECODE(col.nullable,'N','NOT NULL','') not_null,
       DECODE(col.data_type,'NUMBER',col.data_precision ||','||col.data_scale,'') specs,
       col.data_length "LENGTH",
       (SELECT cmt.comments 
        FROM   sys.all_col_comments cmt
        WHERE  cmt.owner = col.owner
        AND    cmt.table_name = col.table_name
        AND    cmt.column_name = col.column_name) comments
FROM   sys.all_tables       tab,
       sys.all_tab_columns  col
WHERE  tab.owner = col.owner
AND    tab.table_name = col.table_name
AND    tab.table_name = 'TEST_IT'
AND    tab.owner = 'ADMBQC'
ORDER  BY tab.table_name, col.column_id;  

[b]COLUMN  TYPE     REQ      SPECS LENGTH  COMMENTS[/b]
FLD1    DATE     NOT NULL       7       Date Added
FLD2    VARCHAR2                5       Description
FLD3    NUMBER            10,0  22	
FLD4    NUMBER            8,2   22

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[TopJack]

Thanks a lot for the ideas - I will give it a go.