All Forums Database
Sethu_85 5 posts Joined 08/09
17 Aug 2009
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.tableswhere 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'

gander_ss 74 posts Joined 02/07
17 Aug 2009

You can achieve this by writing recursive sqlin which the vie name will be the seed for next searchRegards,Subhash

SudhakarP 1 post Joined 08/09
18 Aug 2009

Hi Sethu,Just try this,sel * from dbc.tvm where requesttext like '%lot%'.

Sethu_85 5 posts Joined 08/09
18 Aug 2009

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..

rahulsi 6 posts Joined 10/09
27 Nov 2009

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

teradata_dba 43 posts Joined 01/09
28 Nov 2009

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

teradata_dba

ANIMESH.DUTTA 35 posts Joined 05/09
30 Nov 2009

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.

Animesh Dutta
Teradata Certified Master

mpuglies 1 post Joined 05/10
03 May 2010

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?

Sakshi81 5 posts Joined 12/12
03 Dec 2012

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
 

Qaisar Kiani 337 posts Joined 11/05
05 Dec 2012

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...

Sakshi81 5 posts Joined 12/12
05 Dec 2012

Thanks for reply Qaisar. This will help. I also have the need to know if I change something on table LOT then it impacts V1 and V2. So my next question is- Can I use the same query in reverse analysis i.e. can I use  SHOW QUALIFIED SELECT * FROM LOT and will it give me the V1 and V2? I tried that but it didnt work. Is there any solution for that?
 
Thanks again for help.
 
Regards
Vaishali

 

Qaisar Kiani 337 posts Joined 11/05
05 Dec 2012

Hi Vaishali,
No, the SHOW QUALIFIED SELECT... statement works only for the children (top to bottom). You can't find the parents of a specific table/view through this statement.
However, the view definitions can be found in dbc tables and you will have to write an external program or a UDF to parse the view definitions and build the bierarchy bottom to top.
 

Sakshi81 5 posts Joined 12/12
05 Dec 2012

Thanks again for quick reply Qaisar. I have depeloped procedures and packages in Oracle earlier but I am new to Teradata plus I dont know if I will have access to create any with my access. Is the UDF/Program something that is available on net somewhere? So I can use them?

Qaisar Kiani 337 posts Joined 11/05
05 Dec 2012

I am not sure if such specific UDF exists or not, but you can have a look at the existings UDFs over the teradata website...
http://downloads.teradata.com/download

ulrich 816 posts Joined 09/09
05 Dec 2012

are you looking for this?
http://developer.teradata.com/blog/ulrich/2011/11/extract-and-analyse-database-object-dependencies

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Sakshi81 5 posts Joined 12/12
12 Dec 2012

Thanks Ulrich and Qaisar.
Ulrich,
This is very helpful. Will it give me 100% result? Or do I still need to verify the REQUESTTEXT FROM DBC.TABLES.
 
Thanks again for help!
 
Regards
Vaishali
 
 

ulrich 816 posts Joined 09/09
12 Dec 2012

Check the disclaimer of the post ;->
But so far it looks quite complete in the cases I worked with it - which I do regularly.
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Sakshi81 5 posts Joined 12/12
12 Dec 2012

ok thanks Ulrich :)

Purushotham 87 posts Joined 04/14
21 Apr 2015

HI,
could be pls explain me use of QUALIFIED ..
without using qualified also we can get :SHOW TABLE <TABLE_NAME>
OR sel * from dbc.tables where requexttext like '%table_name%'

Purushotham.M

apant3 1 post Joined 01/14
15 Dec 2015

can any one tell the answer of below question in an automated query form
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.tableswhere 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?
 

You must sign in to leave a comment.