CODEWITHSUNDEEP
oracle-database
CodingWithSpike
CodingWithSpike

Reputation: 43718

Oracle: Is there a way to get the column data types for a view?

For a table in oracle, I can query "all_tab_columns" and get table column information, like the data type, precision, whether or not the column is nullable.

In SQL Developer or TOAD, you can click on a view in the GUI and it will spit out a list of the columns that the view returns and the same set of data (data type, precision, nullable, etc).

So my question is, is there a way to query this column definition for a view, the way you can for a table? How do the GUI tools do it?

Upvotes: 11

Views: 45774

Answers (4)

Joy
Joy

Reputation: 631

Just simply write this query:

SQL> desc TABLE/VIEW NAME;

For example if the table/view name is "department" Then just write:

SQL> desc department;

This will give the list of all fields, its type and default Null info of the table or view.

Upvotes: 4

Alex Poole
Alex Poole

Reputation: 191265

View columns appear in all_tab_columns, so you can query them just as you can tables.

Upvotes: 3

René Nyffenegger
René Nyffenegger

Reputation: 40499

You can use user_tab_columns (or all_tab_columns and dba_tab_columns respectively) regardless if table_name refers to a view or a table.

Upvotes: 13

SQLMenace
SQLMenace

Reputation: 134961

you can use the ANSI catalog views, should work for most RDBMs

select * 
from information_schema.columns c
join information_schema.tables t on c.table_name = t.table_name
where table_type = 'view'

Upvotes: -5

Related Questions