How to fetch columns and table names from a view

jeevan mummadi

Hi All,

I want to fetch columns,tables names from a view. And I have to map those columns related to that tables.

Fetching columns from view
-----------------------------------

SELECT column_name,
data_type
FROM user_tab_columns
WHERE table_name = 'MY_VIEW'

Fetching tables from view
----------------------------------
select name , type , referenced_name , referenced_type
from user_dependencies where name = 'MY_VIEW' and type = 'VIEW' and
referenced_type = 'TABLE'

can you please anyone help me how to map those columns to related tables.

Regards,
Jeevan.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

13y ago
The below query may help you,

SELECT column_name,
data_type,name , type , referenced_name , referenced_type
FROM user_tab_columns c inner join user_dependencies on name= table_name
WHERE table_name = 'MY_VIEW' and type = 'VIEW' and
referenced_type = 'TABLE'

Please try and let me know.
ponveni13y ago
There is a view Called INFORMATION SCHEMA.COLUMNS in Sql server .You can use that view to select the ColumNames,Datatypes of the Columns for Particular Table
Example:
Select COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME='TableName'