Re: Column Name Retrieval

Peter Secor wrote:
>
> Hello All,
>
> I am looking for a stored procedure which will return a list of
> the column names and types for a given table. I have searched through
> the documentation to no avail - any help would be great.
>
> Thanks,
>
> Peter Secor
I know of no procedure povided with ORACLE but this is quite easy to do The table that holds the data is ALL_TAB_COLUMNS (or DBA_TAB_COLUMNS) this has the following description:

 Name                            Null?    Type
 ------------------------------- -------- ----
 OWNER                           NOT NULL VARCHAR2(30)
 TABLE_NAME                      NOT NULL VARCHAR2(30)
 COLUMN_NAME                     NOT NULL VARCHAR2(30)
 DATA_TYPE                                VARCHAR2(9)
 DATA_LENGTH                     NOT NULL NUMBER
 DATA_PRECISION                           NUMBER
 DATA_SCALE                               NUMBER
 NULLABLE                                 VARCHAR2(1)
 COLUMN_ID                       NOT NULL NUMBER
 DEFAULT_LENGTH                           NUMBER
 DATA_DEFAULT                             LONG
 NUM_DISTINCT                             NUMBER
 LOW_VALUE                                RAW(32)
 HIGH_VALUE                               RAW(32)
 DENSITY                                  NUMBER
 NUM_NULLS                                NUMBER
 NUM_BUCKETS                              NUMBER
 LAST_ANALYZED                            DATE
 SAMPLE_SIZE                              NUMBER

The table_name can also hold a view name. If you want to specifically exclude views that you can use a join with ALL_TABLES to ensure that only tables are used (or ALL_VIEWS for the inverse of this).

The column_id is a sequence number of the row within the table thus

	select column_name 
	from all_tab_columns 
	where table_name='ALL_TAB_COLUMNS'
	order by column_id
and 
	select column_name 
	from all_tab_columns
	WHERE table_name='ALL_TAB_COLUMNS'
and
	desc all_tab_columns

All display the columns in the same order (I know in theory this should not be relied upon for the second method but I doubt whether it will ever fail).

Steve Received on Tue Jan 13 1998 - 00:00:00 CET