Get Oracle database tables columns

You can use the following system tables to get metadata about your database. Take this query, for example, which I run using Oracle SQL Developer:

SELECT t.table_name, c.column_name,c.data_type,c.char_used,c.data_precision, c.data_scale, c.owner
FROM all_tables t , all_tab_columns c
WHERE t.TABLE_NAME = c.TABLE_NAME
AND t.OWNER      = c.OWNER
AND t.OWNER      = 'db_username'
ORDER BY t.TABLE_NAME, c.COLUMN_NAME;

Query above returns table name, column name, column data type, column data type unit, column precision and scale (for numbers) and schema owner.

For more information, you can query the DICTIONARY and DICT_COLUMNS views to check what each column means.

SELECT table_name, comments FROM dictionary ORDER BY table_name;

SELECT column_name, comments FROM dict_columns WHERE table_name='ALL_TABLES';

References

Oracle metadata
The Master Key to Oracle’s Data Dictionary
The Self-Documenting Dictionary

Oracle: get list of all tables?
How to get column info from oracle table you don’t own (without using describe)?

Oracle – How to create a readonly user

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: