How to find the associated tables or view in teradata?

Database
Enthusiast

How to find the associated tables or view in teradata?

Hi,

How to find the associated tables or view in teradata?

Let us assume, my table name is 'LOT' and view names are 'V1' and 'V2'

I am using my table 'LOT' in view 'V1' and i am using my view 'V1' in view 'V2'.

I just want to find the views associated with the table 'LOT'.If i use the following query,

select * from dbc.tables

where RequestText like ('%LOT%');

i will be getting only view 'V1'.i wont get the view name 'V2'.In teradata,where internal references are stored?

Can anyone please help me to find out?.I dont want to write the subquery to get the view 'V2'
19 REPLIES
Enthusiast

Re: How to find the associated tables or view in teradata?

You can achieve this by writing recursive sql
in which the vie name will be the seed for next search

Regards,
Subhash

Re: How to find the associated tables or view in teradata?

Hi Sethu,

Just try this,

sel * from dbc.tvm where requesttext like '%lot%'.
Enthusiast

Re: How to find the associated tables or view in teradata?

Hi,

Again its showing only view 'V1' not the View 'V2'.Its showing direct dependency not the indirect dependency.

In teradata, dependencies across the database stored anywhere?.If it is stored in system table,will it be accessible for normal users?

Kindly help me out find the solution..
Enthusiast

Re: How to find the associated tables or view in teradata?

do we have any dbc table having view dependancy details ... same as syscat.viewdep in DB2 ?
Enthusiast

Re: How to find the associated tables or view in teradata?

Guys,

lets understand Teradata is more user friendly since its more of GUI..
i have worked on Oracle database before where most of it is command line.
so wasy way in teradata to find would be log into administrator and go the dbname find the table .. check the refernces .. if it points to a view database and to a view in that again recheck the refernces...

I personally think no point in scribbing your head for writing complex queries yo find mutilple references
Enthusiast

Re: How to find the associated tables or view in teradata?

You can find the same by using the following Query:
It'll show all the Tables/Views referenced...

SHOW SELECT * FROM DatabaseName.TableName;

In the answerset, you'll get different tabs.

Re: How to find the associated tables or view in teradata?

This only works top down, so you can see all dependent children of V2 for the example above. I understand the request and have the same need. I need to be able to identify if we make a change to an underlying table how to easily identify all impacted view layers without having to go reference each and every object. Has anyone solved this problem in a more automated fashion?
Enthusiast

Re: How to find the associated tables or view in teradata?

Hi All,

I have similar task to do. With respect to this example, I would like to know the dependency of view V2 on view V1 and then on table LOT.

Is there any way to find all references using one query?

Could you please help?

Thanks in advance.

Regards

Vaishali

Enthusiast

Re: How to find the associated tables or view in teradata?

If you are looking for something that returns you the dependency in some table format then unfortuantely nothing like this exists.

But you can try SHOW QUALIFIED SELECT * FROM V2.

This will return the DDL of all the dependent views and tables...