All Forums Database
BBarkalow 3 posts Joined 09/09
04 Sep 2009
Stored Procedures and the Data Dictionary

Does anyone know if there is a data dictionary view or table that contains the source code for stored procedures? The RequestText column in DBC.tvm seems to be a pointer to another location, but I can't find it anywhere in the documentation. SQL Assistant is able to display the formatted text, so it has to exist somewhere, no?Many Thanks!

BBarkalow 3 posts Joined 09/09
10 Sep 2009

Does anyone have a suggestion on how to pursue an answer to this? I need to perform comprehensive searches across a large number of stored procedures and macros, and doing so one at a time through TD SQL Assistant is error-prone and extremely tedious. SQL Assistant obviously can display the source code, including however it was formatted at compile time, so there must be a way to either search using SQL, or at a minimum dump all of the source code out to a file and use grep/sed/awk.

BBarkalow 3 posts Joined 09/09
18 Sep 2009

Well, it isn't a perfect solution, but I generated SHOW PROCEDURE statements for all of the procs, then exported them to a text file with BTEQ. This lets me find out (quickly) what stored procedures are accessing or updating a table. It has limitations, like it may be hard to tell if a particular line of code has been commented with /* */ but it is better than nothing.

Jimm 298 posts Joined 09/07
19 Sep 2009

DBC.TableText contains the full text for tables, views and macros. I have used it for these, check it out for procedures.

dnoeth 4628 posts Joined 11/04
20 Sep 2009

The source code of a SP is not stored anywhere within the dbc tables.If you explain a SHOW PROCEDURE you'll see it's stored as a special row within the SP "table".Or you might get a 5535 error: No SPL source text available.The only way to find out about dependencies is installing/using the Teradata MDS (Metadata Services).Dieter

Dieter

You must sign in to leave a comment.