Board index » delphi » How to retrieve tables and columns in Oracle with a SQL statement

How to retrieve tables and columns in Oracle with a SQL statement

Hi All,

I am migrating a database from Interbase to oracle, until now everything is
Ok, in my program I read the tables and columns from the database using
SELECT * FROM RDB$RELATIONS for tables and SELECT * FROM RDB$RELATION_FIELDS
for columns, anyone knows how to do this in Oracle ?

Regards.

 

Re:How to retrieve tables and columns in Oracle with a SQL statement


Hi!

You can Select Tables and Collums with  the following Views:

Only Tables:

select * from all_tables
where owner = 'ICIS';  <- your Schema-Owner
You will need the Attribut TABLE_NAME from this Selection.

Colums:

select * from all_tab_columns
where OWNER = 'ICIS';

Some of the importent Attributes you will need from this Selection:

- TABLE_NAME
- COLUMN_NAME
- DATA_TYPE
- DATA_LENGTH
- DATA_PRECISION
- DATA_SCALE
- NULLABLE
-  ...

Michael D. ;)

Re:How to retrieve tables and columns in Oracle with a SQL statement


Hi,

to expand on Michaels reply, rather than using all_tables and
all_tab_columns with the where clause for your schema, if your schema is
the object's owner it is obviously much faster to use the views
USER_TABLES and USER_TAB_COLUMNS

Have a look through ALL_OBJECTS, that will give you a good list of
objects on the database, including system views.

Regards
Bill

In message <3d3cf799$1_2@dnews>, Michael D. <MediaBl...@gmx.net> writes

Quote

>Hi!

>You can Select Tables and Collums with  the following Views:

>Only Tables:

>select * from all_tables
>where owner = 'ICIS';  <- your Schema-Owner
>You will need the Attribut TABLE_NAME from this Selection.

>Colums:

>select * from all_tab_columns
>where OWNER = 'ICIS';

>Some of the importent Attributes you will need from this Selection:

>- TABLE_NAME
>- COLUMN_NAME
>- DATA_TYPE
>- DATA_LENGTH
>- DATA_PRECISION
>- DATA_SCALE
>- NULLABLE
>-  ...

>Michael D. ;)

--
Bill

Re:How to retrieve tables and columns in Oracle with a SQL statement


Thanks,

"spamnothing" <b...@spamnothing.co.uk> escribi en el mensaje
news:wvIbQKMEMTP9EwAE@spamnothing.co.uk...

Quote
> Hi,

> to expand on Michaels reply, rather than using all_tables and
> all_tab_columns with the where clause for your schema, if your schema is
> the object's owner it is obviously much faster to use the views
> USER_TABLES and USER_TAB_COLUMNS

> Have a look through ALL_OBJECTS, that will give you a good list of
> objects on the database, including system views.

> Regards
> Bill

> In message <3d3cf799$1_2@dnews>, Michael D. <MediaBl...@gmx.net> writes

> >Hi!

> >You can Select Tables and Collums with  the following Views:

> >Only Tables:

> >select * from all_tables
> >where owner = 'ICIS';  <- your Schema-Owner
> >You will need the Attribut TABLE_NAME from this Selection.

> >Colums:

> >select * from all_tab_columns
> >where OWNER = 'ICIS';

> >Some of the importent Attributes you will need from this Selection:

> >- TABLE_NAME
> >- COLUMN_NAME
> >- DATA_TYPE
> >- DATA_LENGTH
> >- DATA_PRECISION
> >- DATA_SCALE
> >- NULLABLE
> >-  ...

> >Michael D. ;)

> --
> Bill

Other Threads