Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Iterating through designs tables in a given sequence?
Geoff Muldoon <gmuldoon_remove_this_bit__at_scu.edu.au> wrote:
>x_at_y.z says...
>
>> I'd like to be able to write some SQL that will iterate through all the tables
>> in a given schema, returning a row for each column in each table. That row
>> would contain table name, field name, and various field properties like size,
>> data type, default value...and so-forth.
>
>There are a number of objects owned by the user SYS that you can query.
>
>desc USER_TAB_COLUMNS;
>
>When logged in as the schema owner, try this SQL:
>
>select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
>DATA_SCALE, NULLABLE, DATA_DEFAULT
>from USER_TAB_COLUMNS
>order by TABLE_NAME;
>
>Geoff M
Be sure to check out the functionality provided by the DBMS_METADATA Package in 9i It may save you much hand-coding. Received on Tue Dec 23 2003 - 10:34:42 CST