how do i find references of tables in any procedure in teradat.

Database
Enthusiast

how do i find references of tables in any procedure in teradat.

Hi ,

 

Can Experts Guide me finding the procedures where any specific table is used?

 

Basically i am looking behind procedure which is populating an X table.

 

Nitz1986
12 REPLIES
Enthusiast

Re: how do i find references of tables in any procedure in teradat.

Hi,

I dont think there is any direct method to find this. if you search qrylogsql for the table, it gives you the insert statement, but not the SP name. I would suggest to see the entries in qrylog and see statementtype = call for that ID, take out the queryid and look into qrylogsql, that will give you the call SP statement. This will give you the SPs that has been executed by that ID and then search into those SPs. if there are not many SPs run by that id, you can narrow it down easily.

--Samir Singh

rjg
Supporter

Re: how do i find references of tables in any procedure in teradat.

You can use DBQL.

SyntaxEditor Code Snippet

sel objectdatabasename, objecttablename from dbc.dbqlobjtbl where queryid in(sel queryid from dbc.qrylog where sessionid in(sel sessionid from dbc.qrylog where queryid in (sel queryid from dbc.dbqlobjtbl where objectdatabasename = 'databasename' and objecttablename = 'procedure_name'))) group by 1,2

 Rglass

Enthusiast

Re: how do i find references of tables in any procedure in teradat.

Thanks, The objecttablename = 'procedure_name' should be Procedure name but i am unaware of the SP. I just have the table name

Nitz1986
rjg
Supporter

Re: how do i find references of tables in any procedure in teradat.

Similar idea, just join to tablesv to get only procs.

 

SyntaxEditor Code Snippet

sel d1.db,d1.tb from 
(sel objectdatabasename, objecttablename from dbc.dbqlobjtbl where queryid
in (sel queryid from dbc.qrylog where sessionid in(sel sessionid from dbc.qrylog where queryid in (sel queryid from dbc.dbqlobjtbl where objectdatabasename = 'databasename' and objecttablename ='tablename'))))D1(DB,TB)inner join dbc.tablesv t on d1.db = t.databasename
and d1.tb = t.tablename
and t.tablekind = 'P' group by 1,2;

Rglass

 

Apprentice

Re: how do i find references of tables in any procedure in teradat.

Hi,

The other reply about using Querylog is in the right direction, but you should be able to get a lot closer.

 

Using either dbqlsqltbl or dbqlobjtbl find all QUERYID values that reference the desired table.

Join to dbqlogtbl on QUERYID to where RequestNum <> InternalRequestNum (if these two are different it implies that the request was executed from within an SP)

Find the first request for that RequestNum (lowest QueryID, should be InternalRequestNum = 1 - I think) and that is the original CALL statement.

Find the querytext for that and you should have your SP name.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: how do i find references of tables in any procedure in teradat.

Hi Dave,

 

Which answer you said is right, Mine (teradatauser2) or Rig's ?

--samir

Enthusiast

Re: how do i find references of tables in any procedure in teradat.

Thank you all. i will check with these solution and get back.

Nitz1986
Enthusiast

Re: how do i find references of tables in any procedure in teradat.

Issue:

 

SELECT Failed. 5315: The user does not have SELECT access to DBC.QryLog.QueryID.

 

Users will not have access to this table is what i came to know.

Nitz1986
Enthusiast

Re: how do i find references of tables in any procedure in teradat.

Also,

 

sel queryid

from dbc.dbqlobjtbl

where objectdatabasename = 'DatabaseName'

and objecttablename =' TableName'

 

No Records Found

Nitz1986