Friday, October 31, 2008

Sql query to list out tables available in current data base - Oracle

Once the data base is created successfully we can create user defined objects like tables,procedures,views,triggers,views,indexes,jobs,etc...

What ever we try to create in data base they are listed out or stored in there respective object type tabels with specific object name and object specific attributes.
This is very help full for many kind of applications.
For example ,Application should show all the tables in a data base.

In such case there is a sql query which returns the table (data base object) names with fields available in it.

Oracle supports a table called "user_tab_cols" to store user defined tables/views.
As views also come under table but which are virtual.

So "select * from user_tab_cols" query helps to list out all tables and there attributes available in oracle data base.

To just retrieve table names but not there fields.
select distinct table_name from user_tab_cols.

Note :- The reason for using distinct clause is, since in a table there can be more than one field so this query with out distinct clause returns same table name multiple times as the number of columns it has.

Also note distinct clause plays a vital role in query performance.
If it is not required to use distinct clause please try to avoid because sql optimiser takes more time to list out distinct values always.
So using distinct clause for a primary key or unique field is absurd.

No comments:

Post a Comment