How can we find dipendency in Teradata database

Database

How can we find dipendency in Teradata database

Hi,

Can you please let me know how can find out a particular teradata table is used in how many and which procedures.
Actually in Oracle there is a dictionary table called "dipendency$" using which we can easily find out what are the procedures are using a particular table table and I am just looking for something equivalent of that in Teradata.

Regards,
Koushik
11 REPLIES
Teradata Employee

Re: How can we find dipendency in Teradata database

Hello,

Till Teradata V2R6, no there is no such table. Though, you can always do that by parsing the definitions, but for that you must have good programming skills.

Regards,

Adeel

Re: How can we find dipendency in Teradata database

Thanks for your answer.
Can you please let me know from which version of Teradata it is available.

Regards,
Koushik
Teradata Employee

Re: How can we find dipendency in Teradata database

Exactly this is what I wanted to say....Till V2R6 it is not available, and am not sure about the later versions if they have something similar! :)

Regards,

Adeel

Re: How can we find dipendency in Teradata database

Hi,

Can you please let me know what is the equivalent dictionary table/view in tearadata for user_source/all_source/dba_source of Oracle. Using which I can view the source data for procedure etc.

Teradata Employee

Re: How can we find dipendency in Teradata database

Well, for similar information you can use the table named "DBC.TVM".

If you are looking for this table, to get the database object's definition, then you should be using SHOW command. Some examples are as follows, but complete guide, check the reference:

--Suppose tblTest is a table:
SHOW TABLE tblTest;

--Suppose spTest is a stored-procedure:
SHOW PROCEDURE spTest;

--Suppose macTest is a macro:
SHOW MACRO macTest;

--Suppose udfTest is a user defined-function:
SHOW FUNCTION udfTest;

HTH.

Regards,

Adeel

Re: How can we find dipendency in Teradata database

Hi,

Can we write any database trigger in Teradata which will log in a table the procedure name against those table names which are used in that procedure. Means proc1 a procedure uses two tables called tab1 and tab2, so when proc1 will be compiled then in a table against tab1 and tab2 (in two different rows) the proc1 name or the id will be registered like below :

Tab1 proc1 procedure
Tab2 proc1 procedure

If it is possible can you please provide some basic syntax for database triggers or simething like this.
Teradata Employee

Re: How can we find dipendency in Teradata database

Well, yes Teradata do have triggers and you can do this in them but I am not convinced why you really want to implement such a trigger? And also what type of trigger will you like to use? AFTER/BEFORE? And what kind, UPDATE/DELETE/etc?

If you just want to maintain a table having all the stored-procedures with corresponding tables, you will have to write INSERTS statements yourself. The decision is yours to insert similar row on every run of a trigger or only once manually.

Regards,

Adeel

Re: How can we find dipendency in Teradata database

Hi,

I want a AFTER trigger CREATION/UPDATION of any procedure.
Teradata Employee

Re: How can we find dipendency in Teradata database

Triggers are only created on tables!

Regards,

Adeel