Stored procedures - calling tables and views

Tools

Stored procedures - calling tables and views

I have to analyse 250 stored procedures to identify the tables and views they are using to get data from or inserting into. Is there a view of achieving this other than manually trying to eye-ball code. Even 80% accuracy will suffice. Will be grateful for any directions. Thanks.

2 REPLIES
Senior Apprentice

Re: Stored procedures - calling tables and views

Teradata's MDS (MetaData Services) could provide all this, but it' probably not installed on your site.

Otherwise you might check DBQL, you will need to enable it on Object-level:

Find the UserID/SessionID/RequestNum of SP CALLs in dbc.QryLogV, all statements submitted by an SP will share this combination (while the InternalRequestNum is increased) and then join to QryLogObjectsV.

Dieter

Teradata Employee

Re: Stored procedures - calling tables and views

Teradata MDS will identify the tables and views that a stored procedure referneces. It will also identify if a stored procedure references macros or other stored procedures.

You install Teradata MDS on a client (Windows or Linux) and create the repository as a database in Teradata. You can then extract metadata from one or more Teradata systems by specifying which databases you want to load into the repository. MDS will then extract information from the DBC and sets up the relationships between objects in a database. Prior to 14.10, MDS was distributed as part of the Teradata Utility Pack so you probably already have it. Starting with 14.10 MDS can be obtained through the Web Ordering Tool (WOT).